--- title: "Concepts" description: "The mental model behind OQL. Query shape, schema allowlist, aggregates, and grouping." canonical_url: https://developer.onepagecrm.com/oql/concepts/ source: Markdown mirror of https://developer.onepagecrm.com/oql/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`. ```json { "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](/oql/entities/contacts/) for the field reference for each. ### `select` Which fields to return. Three shapes: ```jsonc "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. ```jsonc "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](/oql/operators/) page. ### `order_by` Sort order. Bare string for ascending, single-key hash for descending: ```jsonc "order_by": ["created_at", { "last_name": "desc" }] ``` When omitted, contacts and actions return in [Action Stream priority order](/getting-started/data-model/#how-the-stream-sorts) (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](/oql/limits-and-errors/). ### `group_by` Group rows by one or more scalar fields, with aggregates per group. Pairs with aggregate expressions in `select`. ```json { "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](/oql/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`. ```json { "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: ```json { "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`. ```json { "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`](#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 `.` in `select`, `where`, and `order_by`. ```json { "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`. ```text 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](/oql/entities/contacts/) 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](/oql/functions/) for the full list. ## Results Every query returns a structured result: ```json { "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.