One-page reference for ObjectStack QuerySchema — filters, sorts, pagination, aggregations, and expand

Query Syntax Cheat Sheet

Quick reference for building queries with the ObjectStack QuerySchema.

Source: packages/spec/src/data/query.zod.ts and packages/spec/src/data/filter.zod.ts
Import: import { QuerySchema, FilterConditionSchema } from '@objectstack/spec/data'

Basic Query Structure

const query = {
  object: 'task',
  fields: ['id', 'title', 'status', 'assigned_to'],
  where: { /* filters */ },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
  offset: 0
};

Filter Operators

Comparison Operators

Operator	Description	Example
`$eq`	Equal to	`{ status: { $eq: 'open' } }`
`$ne`	Not equal to	`{ status: { $ne: 'closed' } }`
`$gt`	Greater than	`{ amount: { $gt: 100 } }`
`$gte`	Greater than or equal	`{ amount: { $gte: 100 } }`
`$lt`	Less than	`{ amount: { $lt: 1000 } }`
`$lte`	Less than or equal	`{ amount: { $lte: 1000 } }`

Set Operators

Operator	Description	Example
`$in`	Value is in array	`{ status: { $in: ['open', 'pending'] } }`
`$nin`	Value is NOT in array	`{ status: { $nin: ['closed', 'cancelled'] } }`

Range Operator

Operator	Description	Example
`$between`	Value is in range (inclusive)	`{ price: { $between: [10, 100] } }`

String Operators

Operator	Description	Example
`$contains`	String contains substring	`{ title: { $contains: 'urgent' } }`
`$notContains`	String does NOT contain substring	`{ title: { $notContains: 'spam' } }`
`$startsWith`	String starts with	`{ email: { $startsWith: 'admin' } }`
`$endsWith`	String ends with	`{ email: { $endsWith: '@acme.com' } }`

Null / Existence Operators

Operator	Description	Example
`$null`	Value is null	`{ deleted_at: { $null: true } }`
`$exists`	Field has a value	`{ phone: { $exists: true } }`

Date, Datetime & Time Comparands

On SQL-backed objects, filter values on temporal fields are canonicalised by the same functions the driver's write path uses, so the two sides of a comparison can never disagree about shape:

Field type	Canonical comparand	Semantics
`date`	`YYYY-MM-DD`	Timezone-naive calendar day. A `Date` collapses to its UTC day; a longer ISO string is truncated to its leading date.
`datetime`	`YYYY-MM-DDTHH:MM:SS.sssZ`	A UTC instant. A `Date`, an epoch-millisecond number, a bare `YYYY-MM-DD` (→ midnight UTC, but see the whole-day rule below) and a zone-naive `YYYY-MM-DD HH:MM:SS` (→ read as UTC) all fold to this one form.
`time`	`HH:MM:SS`, with `.fff` only when the milliseconds are non-zero	Timezone-naive wall clock. `'14:30'` and `'14:30:00'` canonicalise identically, so they are the same filter.

{
  object: 'meeting',
  where: {
    meeting_day: { $gte: '2026-01-01' },           // date     → '2026-01-01'
    starts_at:   { $gte: '2026-01-01T00:00:00Z' }, // datetime → '2026-01-01T00:00:00.000Z'
    start_time:  { $between: ['09:00', '18:00'] }  // time     → '09:00:00' / '18:00:00'
  }
}

A bare calendar day as an UPPER bound means the whole day

Canonicalisation settles a comparand's shape; which instant a bare YYYY-MM-DD denotes additionally depends on the side of the comparison it sits on:

Operator	A bare `YYYY-MM-DD` means
`$gte` / `$gt` / `$lt`	that day's `00:00:00.000`
`$lte`, and the max of a `$between`	the whole day — compiled half-open, as `< next day`

So { created_at: { $gte: '2026-01-01', $lte: '2026-03-31' } } includes everything recorded on March 31st, not just the instant it began. Without that rule a dashboard window ending "today" silently dropped every row created after midnight (#3777). Pass a full ISO timestamp when you want an exact instant — only the day-granular string carries whole-day intent:

{ created_at: { $lte: '2026-03-31' } }              // through all of March 31st
{ created_at: { $lte: '2026-03-31T12:00:00.000Z' } } // up to noon exactly

On a date field the two forms are equivalent (< next day and <= day order identically over YYYY-MM-DD text), so the rule needs no special handling there. It applies uniformly across the SQL drivers, the in-memory and MongoDB drivers, analytics windows, and the RLS write-side check evaluator.

Canonicalisation runs on every SQL dialect, not just SQLite — a zone-naive string bound into a Postgres timestamptz would otherwise be read in the server's timezone. MySQL is the one dialect that cannot parse the T/Z spelling, so the driver binds the same instant as a MySQL datetime literal — a physical respelling only; every layer above the bind (filter authoring, API payloads, CEL) stays on the canonical …Z form. Values the driver cannot interpret (empty strings, junk) pass through untouched rather than being silently rewritten.

Logical Operators

`$and` — All conditions must match

{
  where: {
    $and: [
      { status: { $eq: 'open' } },
      { priority: { $in: ['high', 'critical'] } }
    ]
  }
}

`$or` — Any condition can match

{
  where: {
    $or: [
      { status: { $eq: 'open' } },
      { status: { $eq: 'pending' } }
    ]
  }
}

`$not` — Negate a condition

{
  where: {
    $not: { status: { $eq: 'closed' } }
  }
}

Nested Logic

{
  where: {
    $and: [
      { type: { $eq: 'bug' } },
      {
        $or: [
          { priority: { $eq: 'critical' } },
          { assigned_to: { $null: true } }
        ]
      }
    ]
  }
}

Sorting

Sort results with orderBy (array of sort nodes):

{
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'created_at', order: 'asc' }
  ]
}

Property	Type	Description
`field`	`string`	Field name to sort by
`order`	`'asc' \| 'desc'`	Sort direction

Over the REST/protocol ingress, orderBy also accepts '-created_at', ['-created_at'] and {created_at: 'desc'}, all normalized to the node array above. A sort naming a field the object does not have, an order that is neither asc nor desc, or a dotted path into a related record (account.company_name — sort reaches only the queried object's own columns) is 400 INVALID_SORT there rather than a dropped sort. Internal callers reaching engine.find() directly are unaffected.

Pagination

Offset-Based Pagination

{
  limit: 20,    // Records per page (max varies by config)
  offset: 40    // Skip first 40 records (page 3)
}

Walking the pages visits every row exactly once, whatever you sort by — or whether you sort at all. The SQL and MongoDB drivers get there by ordering on a unique column of their own, on top of whatever orderBy you gave, because a sort key like status does not identify a row and no backend promises equal keys keep the same arrangement between two queries. Without that, page 2 repeats a row page 1 already showed and skips one nobody ever sees — every page full, every row real, the two halves of the symptom several screens apart (objectui#3106, #4363).

The guarantee attaches to limit/offset, so it costs nothing on the reads that do not paginate: a query with neither is returned in whatever order the backend chooses, exactly as before.

Keyset Pagination — a `where` predicate on the sort key

query.cursor was removed in @objectstack/spec 17 (#4286): nothing on the server ever read it, so a cursor query silently returned the same first page every time. The key is tombstoned and QueryBuilder.cursor() was removed with it. Express the keyset directly — seek past the last row instead of offsetting:

// Next page after `last` — every driver executes this, with canonicalised comparands
{
  where: { created_at: { $gt: last.created_at } },
  orderBy: [{ field: 'created_at', order: 'asc' }],
  limit: 20
}

(A first-class cursor, if ever designed, will be a response-minted opaque token — the pattern the metadata-revision / flow-run / notification list endpoints already use.)

Field Selection

Select Specific Fields

{
  fields: ['id', 'title', 'status', 'created_at']
}

{
  fields: [
    'id',
    'title',
    { field: 'assigned_to', alias: 'assignee' }
  ]
}

This object form of a field node isn't wired up for top-level fields projection. When the object's schema is registered, the engine's unknown-field filter compares each entry against the schema's field names via String(f), so an object entry never matches and is silently dropped from the projection — the aliased field is simply missing from results, no error.

Dotted relationship paths ('owner.name') fare no better: the unknown-field filter validates only the head segment and keeps the path, but there is no populate step anywhere in the engine, so the SQL driver selects owner.name verbatim and the database rejects it. What you get back depends on whether the driver recognises that dialect's error text (no such column, or column … does not exist): if it does, its recovery retry re-runs the query as SELECT * and you get every column; if it doesn't, the error is rethrown. Either way there is never an owner.name key.

Use expand to pull in a relationship's fields instead.

Both of these are engine.find() behaviours, reached by internal callers. Over the REST/protocol ingress a projection column that names no field at all is 400 INVALID_FIELD — including the case where no requested column is known, which used to fall back to SELECT * and answer a one-column request with every column.

Load related records through the reference field types — lookup, master_detail, user and tree (REFERENCE_VALUE_TYPES) — with expand. Each key is a relationship field name; the value is a nested query that can select fields, filter, and expand further (max depth 3 — a fixed constant, not configurable).

{
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      where: { is_active: { $eq: true } },     // AND-merged with the batch lookup
      expand: { org: { object: 'org' } }        // nested expand
    }
  }
}

The engine resolves expand via batch $in queries (driver-agnostic), so it works on every driver. A nested limit / offset is not forwarded at all — one batch query serves every parent, so a per-parent window cannot be expressed. A nested orderBy is forwarded to that batch query, but it has no observable effect: the expanded records are re-keyed to each parent by id, so a multi-value relationship keeps the order stored on the parent record. Paginate or sort by querying the related object directly.

Aggregations

Available Functions

Function	Description	Example
`count`	Count records	`{ function: 'count', alias: 'total' }`
`sum`	Sum numeric field	`{ function: 'sum', field: 'amount', alias: 'total_amount' }`
`avg`	Average numeric field	`{ function: 'avg', field: 'rating', alias: 'avg_rating' }`
`min`	Minimum value	`{ function: 'min', field: 'price', alias: 'min_price' }`
`max`	Maximum value	`{ function: 'max', field: 'price', alias: 'max_price' }`
`count_distinct`	Count unique values	`{ function: 'count_distinct', field: 'category', alias: 'categories' }`

count_distinct is not yet lowered by the SQL drivers. They map count/sum/avg/min/max and refuse it as a capability gap — 501 NOT_IMPLEMENTED, "declared but not implemented by this backend" — rather than as a caller mistake, because the query is spelled correctly and the gap is the backend's (#5907). It works on the MongoDB driver and on the engine's in-memory fallback, and its SQL lowering (COUNT(DISTINCT field)) is scheduled.

Removed in 17. array_agg and string_agg were declared here and compiled by no SQL backend, which left "what can this backend actually compute" unpredictable to the author. Both were retired (#6188, ADR-0049 enforce-or-remove): a query carrying either is now refused at parse with a prescription. There is no replacement in the query vocabulary — read the rows with an ordinary fields query and shape them in the caller, or materialise the roll-up as a stored field. os migrate meta --from 16 rewrites affected dataset measures.

Over the REST/protocol ingress, groupBy and aggregations are validated before they reach any driver. A field the object does not have is 400 INVALID_FIELD — the in-memory fallback used to collapse an unknown groupBy column into one null-keyed bucket, and to answer sum(<typo>) with 0. A value the spec cannot read (a non-array, an entry naming no field, a function or dateGranularity outside the enums above, a missing alias) is 400 INVALID_QUERY — those shapes used to be ignored, returning ungrouped raw rows. count with no field (or field: "*") is the one legitimate field-less form and passes. Internal callers reaching engine.aggregate() directly are unaffected.

Aggregation Example

{
  object: 'order',
  aggregations: [
    { function: 'count', alias: 'order_count' },
    { function: 'sum', field: 'total', alias: 'revenue' },
    { function: 'avg', field: 'total', alias: 'avg_order' }
  ],
  groupBy: ['status'],
  having: { order_count: { $gt: 10 } }
}

having is enforced since #4286 (ADR-0049 enforce-or-remove, resolved to enforce): the engine applies it itself AFTER aggregation, identically on the native-driver path and the in-memory fallback, and the REST findData() aggregate branch forwards it. Its namespace is the aggregated row's own columns — aggregation aliases (order_count) and groupBy projections — with the ordinary FilterCondition operators and $and/$or/$not. An unknown operator is rejected loudly rather than ignored. Native SQL HAVING pushdown can come later behind a driver capability flag without changing these semantics.

Joins — removed

query.joins was removed in @objectstack/spec 17 (#4286, ADR-0049 enforce-or-remove): no driver's find() ever executed a join — the SQL, in-memory, and MongoDB drivers all ignored the array, so it only ever declared a capability that did not run. The key is tombstoned: authoring it is a tsc error, and a query carrying it (even joins: []) fails to parse with the upgrade prescription. The JoinNode/JoinType/JoinStrategy exports left with it.

Use expand for relationship traversal — the live spelling for related records — or a dotted fields path ('customer.name') for a single related column:

{
  object: 'order',
  fields: ['id', 'total'],
  expand: {
    customer_id: { object: 'customer', fields: ['name', 'email'] }
  }
}

Full-Text Search

search takes the query text; the server resolves which fields to search from object metadata (ADR-0061). Pass searchFields to narrow that set — it is intersected with what the object allows, so it can only narrow, never widen.

{
  object: 'article',
  search: 'kubernetes deployment',
  searchFields: ['title', 'body', 'tags']   // optional
}

The structured form is equivalent and carries the experimental knobs below — query and fields mean exactly what search and searchFields do:

{
  object: 'article',
  search: {
    query: 'kubernetes deployment',
    fields: ['title', 'body', 'tags']
  }
}

Property	Type	Description
`query`	`string`	Search text (the bare `search: '…'` string form)
`fields`	`string[]`	Fields to search (optional — defaults to all searchable; the top-level spelling is `searchFields`)
`fuzzy`	`boolean`	`[EXPERIMENTAL — not enforced]` Fuzzy matching for typo tolerance
`operator`	`'and' \| 'or'`	`[EXPERIMENTAL — not enforced]` How to combine search terms
`boost`	`Record<string, number>`	`[EXPERIMENTAL — not enforced]` Field relevance weights
`minScore`	`number`	`[EXPERIMENTAL — not enforced]` Minimum relevance score (0–1)
`language`	`string`	`[EXPERIMENTAL — not enforced]` Language for stemming/stopwords
`highlight`	`boolean`	`[EXPERIMENTAL — not enforced]` Return highlighted matches

Only query and fields are implemented. The engine expands search into a driver-agnostic $and-of-$or-of-$contains filter (ADR-0061) — fuzzy, operator, boost, minScore, language, and highlight are accepted by QuerySchema but read nowhere in expandSearchToFilter() / normalizeSearch(), so they have no effect; since #4286 their .describe() markers say so. Multiple search terms are always AND-ed regardless of operator.

fields (and its query-parameter spelling, ?searchFields= / ?$searchFields=) can only narrow the scan within the server-resolved searchable set — the object's declared searchableFields, or a text-like auto-default when none are declared. Over the REST/protocol ingress a name outside that set is 400 INVALID_FIELD, with distinct messages for a field that does not exist and a real field that is not searchable. The engine used to drop unknown names and fall back to the full searchable set — a parameter that exists to narrow a search, silently widening it. Internal callers reaching engine.find() directly are unaffected.

search scans the queried object's own columns. A dotted path (project_id.name) is not a search target — unlike fields / sort / filters, the search axis does not resolve traversal, and a dotted entry is refused, not silently dropped:

Unknown field 'project_id.name' on object 'task'. '$searchFields' narrows which
columns 'search' scans, so a name the object does not declare cannot narrow
anything — and the engine used to drop it and scan the default columns instead,
answering a NARROWER search with a WIDER one. 'search' scans this object's own
columns; a related record's column cannot be a search target.

The answer is a mirror field: copy the related record's title into a stored field on this object and declare that field searchable. A task list searched by project name gets a project_name text column on task, maintained on write and listed in task.searchableFields. It has to be a stored field — a formula field is virtual, so no driver has a column for $contains to scan. Cross-object search paths are rejected by design, not pending. Full recipe (the hooks that keep the mirror fresh, and the lint wording) in Schema Design → Searching by a related record's title.

Pinyin recall (Chinese deployments)

When pinyin search is enabled (OS_SEARCH_PINYIN_ENABLED — auto-on when the stack's i18n config lists any zh-* locale, see Environment Variables → Search), records with CJK names are also found by typing their full pinyin or initials: searching zhangwei or zw matches a record named 张伟, alongside the normal CJK and latin matching. This is transparent to queries and clients — the platform maintains a hidden __search companion column derived from each object's display/name field and ORs it into the expanded filter; fields semantics, searchableFields, and drivers are unchanged (ADR-0098). Relevance ranking and typo tolerance remain Tier-2 (native FTS) and are not part of this.

Coverage — every object with a resolvable display/name field gets the companion, including sys_user: the people picker (which sends a plain $search against sys_user) finds users by pinyin with zero per-object or per-field configuration. Users created through any write path — sign-up, admin rename, engine writes — are searchable immediately.

Pre-existing rows are reconciled automatically: the first boot after the switch turns on runs a paged, idempotent backfill over every object that carries the companion column. For bulk imports that bypassed write hooks at runtime, rebuildSearchCompanion (exported by @objectstack/plugin-pinyin-search) recomputes the column on demand.

Window Functions — removed from the request surface

query.windowFunctions was removed in @objectstack/spec 17 (#4286): ObjectQL.find() / .aggregate() and the POST /api/v1/data/:object/query route never routed it anywhere, so sending it had no effect. The key is tombstoned — a query carrying it fails to parse with the upgrade prescription — and the WindowFunction/WindowSpec/WindowFunctionNode exports left with it.

Window functions remain a SQL-driver door: findWithWindowFunctions(), callable directly on a SQL driver instance (it is not on the IDataDriver contract). Its input is the driver's own flat shape:

const ranked = await sqlDriver.findWithWindowFunctions('employee', {
  windowFunctions: [
    {
      function: 'rank',
      alias: 'salary_rank',
      partitionBy: ['department'],
      orderBy: [{ field: 'salary', order: 'desc' }]
    }
  ]
});

Pass options.tenantId on a multi-tenant deployment. Like find(), this door is tenant-scoped only when the caller supplies it — the example above omits it, so it reads across every tenant. That is the driver layer's documented contract (seed scripts and cross-org tooling depend on the unscoped path), but it is a decision to make deliberately.

Until #6792 the door ignored options.tenantId even when you did pass it and returned every tenant's rows regardless. It now routes through the driver's applyTenantScope chokepoint like every other read.

For request-level analytics, use aggregations + groupBy, or model rankings in report/dashboard metadata.

Distinct & Group By

Distinct Records — removed flag, three live spellings

The top-level query.distinct flag was removed in @objectstack/spec 17 (#4286): no driver's find() ever applied it, and its only observable effect was mis-wired — it silently suppressed the REST list count while still returning duplicate rows (the count is truthful again). The key is tombstoned and QueryBuilder.distinct() was removed with it. What actually deduplicates:

// Unique combinations → groupBy
{ object: 'task', groupBy: ['category'] }

// Deduplicated count → count_distinct
{ object: 'task', aggregations: [{ function: 'count_distinct', field: 'category', alias: 'categories' }] }

A separate driver.distinct(object, field) method also exists on the SQL and in-memory drivers (driver-level; not called by ObjectQL.find()/.aggregate()).

Group By with Having

{
  object: 'order',
  fields: ['customer_id'],
  aggregations: [
    { function: 'sum', field: 'total', alias: 'total_spent' }
  ],
  groupBy: ['customer_id'],
  having: { total_spent: { $gt: 1000 } }
}

As noted under Aggregations above, having filters the aggregated rows engine-side — total_spent here is the aggregation alias it references.

Date Bucketing in `groupBy`

A groupBy entry is either a bare field name or a structured node that buckets a date / datetime column into uniform periods:

{
  object: 'deal',
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'revenue' }
  ],
  groupBy: ['stage', { field: 'closed_at', dateGranularity: 'quarter' }]
}

dateGranularity accepts day | week | month | quarter | year. The engine pushes bucketing down to the driver only when that driver advertises the granularity via supports.queryDateGranularity — SQLite reports week: false, for instance, so a weekly bucket falls back to fetching rows and bucketing in memory. The fallback is transparent to the query — you get the same buckets either way.

Buckets are computed in UTC. A non-UTC reference timezone is only reachable through ObjectQL.aggregate(object, { …, timezone }); the POST /api/v1/data/:object/query route forwards only where / groupBy / aggregations, so a query sent over REST always buckets on UTC calendar boundaries.

The optional alias on a structured groupBy node is honoured only on the in-memory path. The SQL driver ignores it and always projects the bucket under the field name, so don't depend on the aliased key being present.

Common Query Patterns

List with Pagination

{
  object: 'task',
  fields: ['id', 'title', 'status', 'assigned_to', 'created_at'],
  where: { status: { $ne: 'archived' } },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 25,
  offset: 0
}

Search with Filter

{
  object: 'contact',
  search: { query: 'john', fields: ['first_name', 'last_name', 'email'] },
  where: { is_active: { $eq: true } },
  orderBy: [{ field: 'last_name', order: 'asc' }],
  limit: 50
}

Dashboard Aggregation

{
  object: 'deal',
  aggregations: [
    { function: 'count', alias: 'deal_count' },
    { function: 'sum', field: 'amount', alias: 'pipeline_value' },
    { function: 'avg', field: 'amount', alias: 'avg_deal_size' }
  ],
  groupBy: ['stage'],
  where: { closed_at: { $null: true } }
}

Recent Activity

{
  object: 'activity',
  fields: ['id', 'type', 'description', 'user', 'created_at'],
  expand: {
    user: { object: 'user', fields: ['name'] }
  },
  where: {
    created_at: { $gte: '2026-01-01T00:00:00Z' }   // canonicalised to '…T00:00:00.000Z'
  },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 10
}

Query Syntax Cheat Sheet

On this page