Concepts

OQL queries are plain JSON objects. There is no string parser and no SQL dialect to learn. Every query is a hash with one required key, from, plus optional select, where, order_by, limit, group_by, having, and distinct.

{
  "from": "contacts",
  "select": ["first_name", "last_name", "company"],
  "where": { "status_id": "lead", "owner_id": "ME()" },
  "order_by": [{ "created_at": "desc" }],
  "limit": 25
}

The clauses

from (required)

The entity you are querying, plural: contacts, companies, deals, actions, notes, calls, meetings (singular works too). See Entities for the field reference for each.

select

Which fields to return. Three shapes:

"select": "*"                           // every exposed field
"select": ["first_name", "last_name"]   // named fields
"select": ["count()", {"sum": ["amount"]}]   // aggregates

select is optional. Omitting it is equivalent to "*". Some fields are output only and cannot be selected by name (display labels resolved from an _id, for example status and lead_source on contacts); the entity reference flags these.

where

Filter conditions. All sibling keys are AND’d together — there is no OR. Use in for several values of one field ({ "status": { "in": ["won", "lost"] } }). When the alternatives span different fields, run separate queries and combine the results.

"where": {
  "status": "won",
  "amount": { ">": 1000 },
  "close_date": "LAST_QUARTER()"
}

Bare values mean equality. Operators only when you need them. The full operator reference and the type compatibility matrix are on the Operators page.

order_by

Sort order. Bare string for ascending, single-key hash for descending:

"order_by": ["created_at", { "last_name": "desc" }]

When omitted, contacts and actions return in Action Stream priority order (the same order the OnePageCRM app uses). Other entities return in document order. Only fields marked sortable can appear in order_by.

limit

Maximum rows to return. Capped at 1000; values above that are rejected, not silently clamped. See Limits.

group_by

Group rows by one or more scalar fields, with aggregates per group. Pairs with aggregate expressions in select.

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

Up to 3 group fields. Date and time fields require a date bucketing function (DAY, WEEK, MONTH, QUARTER, YEAR); raw timestamp grouping is rejected. Full rules on the Functions page.

having

Filters grouped results after aggregation. Keys refer to aggregate output columns (count, sum_amount, avg_amount, …) or fields listed in group_by, not to entity fields. Only valid alongside group_by.

{
  "from": "deals",
  "select": ["owner_id", "count()", { "sum": ["amount"] }],
  "where": { "status": "won" },
  "group_by": ["owner_id"],
  "having": { "count": { ">": 5 }, "sum_amount": { ">=": 10000 } }
}

Same operators as where, except like (aggregate columns are numeric). Date functions like TODAY() are not resolved inside having: filter dates in where, which runs before grouping.

distinct

Set distinct: true to return each unique combination of the selected fields once, without aggregating:

{
  "from": "contacts",
  "select": ["status_id", "owner_id"],
  "distinct": true
}

Requires a select of named fields ("*" and aggregates are rejected), and every selected field must be groupable. Cannot be combined with group_by (distinct over a set of fields is a group_by with no aggregates). Date and time fields need a bucketing function, so use group_by for those instead.

Aggregates

Seven aggregate functions: count, sum, avg, min, max, median, percentile.

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

• count() takes no field and counts every matching row. With a field argument it counts non-null values.
• sum, avg, min, max, median, and percentile require a numeric field with the aggregatable trait. min and max also accept sortable date and time fields (earliest and latest value).
• percentile takes the percentile as a required second argument in (0, 1): { "percentile": ["amount", 0.9] } returns a p90_amount column. median is shorthand for the 0.5 percentile; its column is median_amount.
• Append "distinct" as the last argument to dedupe the field’s values before aggregating: { "count": ["owner_id", "distinct"] } returns count_distinct_owner_id.
• Without group_by, aggregates apply to the whole result set and return a single row.
• With group_by, every plain field in select must also appear in group_by. Mixing plain fields and aggregates without grouping is rejected. Grouped results can be filtered after aggregation with having.

Lookups (related fields)

Several entities reference a parent record by ID — a deal has a contact_id, a note has a contact_id and an optional deal_id. A lookup pulls fields off that related record inline, without a second query, by naming them <lookup>.<field> in select, where, and order_by.

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

Deals, actions, calls, and meetings look up their contact; notes look up both contact and deal. Contacts and companies have no lookups — nothing references out from them, so query the child entity and filter by its foreign key (contact_id, company_id) instead. Each entity page lists the lookups it exposes.

Two rules:

• group_by and aggregates do not apply to lookup fields. Group by the foreign key you already hold (contact_id), and filter the lookup field in where.
• Lookup fields obey the same allowlist. contact. followed by an unknown or non-filterable contact field fails with a ValidationError that names the lookup.

The schema is an allowlist

OQL only sees fields declared in the public schema. A query that references an unknown entity, an unknown field, or a field that does not support the requested trait is rejected up front with a clear validation error. There is no silent fallback to null.

ValidationError: Unknown field 'foo' on entity 'contacts'
ValidationError: Field 'background' is not filterable
ValidationError: Operator '<' is not compatible with type 'string'
  on field 'first_name'

This means a typo or schema change surfaces immediately. The entity reference lists every field and every trait it supports.

Timezone

OnePageCRM resolves dates in your user profile’s timezone. Date and time values in where are interpreted in that timezone; date and time values in results are returned in that timezone with an ISO 8601 offset. The same timezone is used on both sides so reasoning stays consistent.

TODAY(), THIS_WEEK(), LAST_QUARTER(), and DAYS_AGO all resolve in your profile’s timezone. See Functions for the full list.

Results

Every query returns a structured result:

{
  "rows": [
    { "first_name": "Alice", "company": "Acme" },
    { "first_name": "Bob",   "company": "Globex" }
  ],
  "row_count": 2
}

truncated: true is added (the key is omitted otherwise) when the requested limit was hit and more rows exist beyond it. Aggregate queries with no group_by always return exactly one row.