---
title: "Query CRM data with OQL"
description: "Answer real pipeline questions with OQL — filters, sorting, aggregates, grouping, and date helpers, run through an MCP-connected AI client."
canonical_url: https://developer.onepagecrm.com/tutorials/oql/
source: Markdown mirror of https://developer.onepagecrm.com/tutorials/oql/
---

It's Monday morning and you have questions. What's in the pipeline?
How much is it worth? Where is it stuck? How did last quarter actually
go? This tutorial answers all of them with OQL — OnePageCRM's JSON
query language — one question at a time, building from a simple
filter to grouped aggregates with date helpers.

## Where OQL runs today

OQL is available through the OnePageCRM
[MCP server](/mcp/overview/), via its
[`query` tool](/mcp/tools/query/). There is no public REST endpoint
for OQL.

So to follow along, connect an AI client (Claude, ChatGPT, or your
favorite agent) to `https://app.onepagecrm.com/mcp`. The
[MCP overview](/mcp/overview/) covers the endpoint and the supported
clients — for those, there's nothing to register.

Normally you'd just ask the agent a question in plain language and let
it write the OQL itself. In this tutorial we write the queries by
hand, because knowing the language lets you check the agent's work,
debug its misses, and reuse the same queries in your own integration
later. To run a query verbatim, prompt the agent like this:

> Run this exact OQL query with your query tool and show me the raw
> result:
> `{ "from": "deals", "where": { "status": "pending" } }`

Now, the questions.

## "What's in my pipeline?"

Start with the open deals, biggest first. Every OQL query is a JSON
object with one required key — `from`, the entity — plus optional
clauses:

```json
{
  "from": "deals",
  "select": ["name", "amount", "stage"],
  "where": { "status": "pending" },
  "order_by": [{ "amount": "desc" }],
  "limit": 10
}
```

Reading it clause by clause: `where` filters (a bare value means
equals), `select` picks fields, `order_by` sorts (a single-key hash
names the direction explicitly; a bare string field sorts ascending),
`limit` caps the rows. The result:

```json
{
  "rows": [
    { "name": "Initech expansion", "amount": 20000, "stage": 40 },
    { "name": "Acme renewal", "amount": 12000, "stage": 20 },
    { "name": "Globex onboarding", "amount": 8500, "stage": 20 }
  ],
  "row_count": 3
}
```

Every query returns this envelope: `rows` and `row_count`, plus a
`"truncated": true` key that appears only when more rows exist beyond
your `limit` — so you can never mistake a clipped answer for a
complete one.

## "How much is that worth?"

You don't need the deal list to answer this — ask for aggregates
instead of fields. OQL has seven (`count()`, `sum`, `avg`, `min`,
`max`, `median`, `percentile`), and the workhorses are the first
three:

```json
{
  "from": "deals",
  "select": ["count()", { "sum": ["amount"] }, { "avg": ["amount"] }],
  "where": { "status": "pending" }
}
```

Without grouping, aggregates collapse the whole result set into
exactly one row:

```json
{
  "rows": [{ "_id": null, "count": 14, "sum_amount": 187500, "avg_amount": 13392.86 }],
  "row_count": 1
}
```

Aggregate columns are named `<function>_<field>` — `sum_amount`,
`avg_amount` — and a bare `count()` is just `count`. The `"_id": null`
is the ungrouped row's group key; ignore it.

Fourteen open deals, 187,500 in play. Next question.

## "What's due to close this quarter — and which are mine?"

Two new tools here. Date helpers like `THIS_QUARTER()` resolve in your
profile's timezone, so "this quarter" means *your* quarter. And `ME()`
resolves to your own user ID, so the same query works for whoever runs
it. Conditions in `where` are always ANDed together — there is no OR
(use `in` for a set of values, or run separate queries):

```json
{
  "from": "deals",
  "select": ["name", "amount", "expected_close_date"],
  "where": {
    "status": "pending",
    "owner_id": "ME()",
    "expected_close_date": "THIS_QUARTER()"
  },
  "order_by": ["expected_close_date"]
}
```

```json
{
  "rows": [
    { "name": "Acme renewal", "amount": 12000, "expected_close_date": "2026-06-19" },
    { "name": "Initech expansion", "amount": 20000, "expected_close_date": "2026-06-30" }
  ],
  "row_count": 2
}
```

One subtlety worth knowing: pending deals carry an
`expected_close_date`; once a deal is won or lost, its actual
`close_date` is set instead. The
[deals entity reference](/oql/entities/deals/) flags which field
applies when.

## "Where is the pipeline sitting?"

For a breakdown rather than a list, add `group_by`. Pair it with
aggregates, and remember the rule: every plain field in `select` must
also appear in `group_by`:

```json
{
  "from": "deals",
  "select": ["stage", "count()", { "sum": ["amount"] }],
  "where": { "status": "pending" },
  "group_by": ["stage"]
}
```

```json
{
  "rows": [
    { "stage": 10, "count": 6, "sum_amount": 42000 },
    { "stage": 20, "count": 5, "sum_amount": 65500 },
    { "stage": 40, "count": 3, "sum_amount": 80000 }
  ],
  "row_count": 3
}
```

Stage numbers are account-configurable integers (and not necessarily
sequential) — your agent can call the MCP `context` tool to map them
to their labels.

## "How did last quarter actually go?"

Switch the filter from open deals to closed ones and group by owner.
`LAST_QUARTER()` does the date math for you:

```json
{
  "from": "deals",
  "select": ["owner_id", "count()", { "sum": ["amount"] }],
  "where": { "status": "won", "close_date": "LAST_QUARTER()" },
  "group_by": ["owner_id"]
}
```

```json
{
  "rows": [
    { "owner_id": "5417f36d1da4171227000001", "count": 9, "sum_amount": 96000 },
    { "owner_id": "5417f36d1da4171227000002", "count": 4, "sum_amount": 51500 }
  ],
  "row_count": 2
}
```

## "Is that trend up or down?"

One quarter is a data point; a year is a trend. Two new pieces here.
`DAYS_AGO` is the only date helper that takes an argument, so it uses
object form: `{ "DAYS_AGO": [365] }`. And grouping by a date field
requires a bucketing function (`DAY`, `WEEK`, `MONTH`, `QUARTER`,
`YEAR`) — OQL won't group on a raw timestamp:

```json
{
  "from": "deals",
  "select": ["close_date", "count()", { "sum": ["amount"] }],
  "where": {
    "status": "won",
    "close_date": { ">=": { "DAYS_AGO": [365] } }
  },
  "group_by": [{ "MONTH": ["close_date"] }]
}
```

Each row comes back keyed on the start of its month, with the count
and revenue for that month — a closed-won trend line in one query.
Note the operator syntax that appeared in `where`: when equality isn't
enough, wrap the value in a single-operator hash like
`{ ">=": ... }`. The full set (`!=`, `<`, `>`, `in`, `between`,
`like`, null checks) is on the [Operators page](/oql/operators/).

## "Fine — who do I chase today?"

Analysis done, time to act. Actions are queryable too, and this is
where `ME()` and `TODAY()` earn their keep — everything due today or
overdue, assigned to you, still open:

```json
{
  "from": "actions",
  "where": {
    "assignee_id": "ME()",
    "completed": false,
    "date": { "<=": "TODAY()" }
  }
}
```

No `order_by` — that's deliberate. When you omit it, actions come back
in Action Stream priority order, the same order the OnePageCRM app
shows you. Your Monday list, pre-sorted.

## One join without the joins

So far you've pulled raw IDs like `owner_id` and `contact_id`. Often
you want the *related* record's fields instead — the deal's contact
name, their company, their country. A lookup does that inline:
reference a related entity's field as `<lookup>.<field>` in `select`,
`where`, or `order_by`.

```json
{
  "from": "deals",
  "select": ["name", "amount", "contact.last_name", "contact.company"],
  "where": { "status": "pending", "contact.country_code": "US" },
  "order_by": [{ "amount": "desc" }]
}
```

Open US deals, each row carrying the deal *and* its contact's name and
company — no second query, no stitching IDs together afterwards.
`deals`, `actions`, `calls`, and `meetings` look up their `contact`;
`notes` look up both `contact` and `deal`. Grouping
and aggregates stay on the entity's own fields, so group by
`contact_id`, not `contact.company`.

## The guardrails

OQL's limits are strict by design: a query that exceeds one fails with
a clear error instead of silently returning a partial answer.

| Limit | Value |
| --- | --- |
| Max `limit` | 1,000 rows — higher values are rejected, not clamped |
| `group_by` fields | 3 max |
| `like` patterns | 3 wildcards and 100 characters max |
| `where` logic | AND only — no OR |

The schema works the same way: it's an allowlist, so a typo'd field or
an unsupported operator fails fast with a message that says exactly
what's wrong:

```text
Unknown field 'foo' on entity 'contacts'
Operator '<' is not compatible with type 'string' on field 'first_name'
```

That strictness is what makes OQL safe to hand to an AI agent — it
either gets a correct answer or an error it can read and fix. The full
list is on [Limits and errors](/oql/limits-and-errors/).

## Where to go next

- **[OQL recipes](/oql/recipes/)** — copy-paste queries for win rates,
  stuck deals, stale contacts, call volumes, and more.
- **[Entity reference](/oql/entities/contacts/)** — every field on all
  seven core entities (`contacts`, `companies`, `deals`, `actions`,
  `notes`, `calls`, `meetings`), with filter/sort/aggregate flags.
- **[Concepts](/oql/concepts/)** — the full query shape and the rules
  behind aggregates and grouping.
- **[MCP overview](/mcp/overview/)** — the endpoint, the five tools, and
  how to connect a client that isn't pre-registered.