Query CRM data with 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, via its query tool. 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 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:

{
  "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:

{
  "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:

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

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

{
  "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):

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

{
  "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 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:

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

{
  "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:

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

{
  "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:

{
  "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.

”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:

{
  "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.

{
  "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:

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.

Where to go next

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