| title | ObjectQL query syntax — the full specification |
|---|---|
| navTitle | Query Syntax |
| description | Database-agnostic query language with filtering, expand, aggregations, and sorting — aligned with the canonical @objectstack/spec QuerySchema |
import { Search, Filter, GitMerge, BarChart } from 'lucide-react';
ObjectQL queries are expressed as Abstract Syntax Trees (AST) in JSON format. This enables database-agnostic querying—write once, compile to PostgreSQL, MongoDB, SQLite, or any supported driver.
All query syntax in this document follows the canonical QuerySchema defined in @objectstack/spec (packages/spec/src/data/query.zod.ts). Filtering uses the where + MongoDB-style $op object syntax from FilterConditionSchema (packages/spec/src/data/filter.zod.ts).
Traditional SQL:
-- Tightly coupled to PostgreSQL
SELECT c.name, c.email, a.company_name
FROM contact c
LEFT JOIN account a ON c.account_id = a.id
WHERE c.is_active = true AND a.industry = 'tech'
ORDER BY c.created_at DESC
LIMIT 10;ObjectQL (Canonical Spec Format): {/* os:check */}
import type { QueryAST } from '@objectstack/spec/data';
const query: QueryAST = {
object: 'contact',
fields: ['name', 'email', 'account'],
where: { is_active: true },
// Related records are loaded through `expand` — not through a JOIN and not
// through a dotted `account.industry` path (see §2 and §4).
expand: {
account: { object: 'account', fields: ['company_name'] },
},
orderBy: [{ field: 'created_at', order: 'desc' }],
limit: 10,
};Runtime compilation:
- PostgreSQL / MySQL → parameterised single-table SQL (
@objectstack/driver-sql); related records are a second, batched$inread, not a JOIN - MongoDB → Native queries + aggregation pipeline (
@objectstack/driver-mongodb) - SQLite → Portable SQL, in-process or in-browser via
@objectstack/driver-sqlite-wasm - In-Memory → In-process evaluation, no external database (
@objectstack/driver-memory)
The canonical query structure is defined by QuerySchema in @objectstack/spec:
import type { QueryAST } from '@objectstack/spec/data';
// QueryAST — full structure
interface QueryAST {
object: string; // Target object (required)
fields?: FieldNode[]; // Projection (SELECT) — field names
where?: FilterCondition; // Filtering (WHERE) — MongoDB-style $op
search?: string | FullTextSearch; // Full-text search — the query text (canonical), or the structured form
searchFields?: string[]; // Narrow the search (server-intersected — narrows only, never widens)
orderBy?: SortNode[]; // Ordering (ORDER BY)
limit?: number; // Max records (LIMIT)
offset?: number; // Skip records (OFFSET)
top?: number; // Alias for limit (OData compat)
aggregations?: AggregationNode[]; // Aggregation functions
groupBy?: GroupByNode[]; // GROUP BY targets (string | object)
having?: FilterCondition; // HAVING — engine-enforced after aggregation
expand?: Record<string, QueryAST>; // Recursive relation loading
}QuerySchema validates the whole structure above, but IDataEngine.find() plus the
shipped drivers run a subset. SqlDriver.find() builds only where / orderBy /
limit / offset / fields (packages/drivers/driver-sql/src/sql-driver.ts), and
expand is resolved afterwards by the engine as a batched $in read
(packages/objectql/src/engine.ts). These members validate but are not executed
on the find() path:
| Member | Status |
|---|---|
aggregations[].filter |
[EXPERIMENTAL — not enforced] — a SQL FILTER (WHERE …) affordance neither the SQL builders nor the in-memory fallback applies |
search.fuzzy / boost / operator / minScore / language / highlight |
[EXPERIMENTAL — not enforced] — only query and fields drive the expansion |
top is the exception that is honored — the engine normalises it to limit.
The #4286 sweep (ADR-0049 enforce-or-remove) settled every other declared-but-inert
member. Removed — tombstoned in @objectstack/spec 17, so a query carrying one
fails to parse with the upgrade prescription and authoring it is a tsc error:
joins (related records are read through expand), windowFunctions (a SQL-driver
door remains: SqlDriver.findWithWindowFunctions()), cursor (express the keyset as
a where predicate on the sort key — §7), and distinct (unique values via
groupBy / count_distinct / the drivers' distinct() door; its only observable
effect was suppressing the REST list count, which is truthful again). Enforced:
having (§5). The experimental flags above are tracked in the liveness ledger
(packages/spec/liveness/query.json).
One member of AggregationNode was settled separately, in #6815: the
per-aggregation distinct flag is removed on the same terms. It escaped the
#4286 sweep because that sweep asked which members no executor reads and this one had
a reader — one out of six. The engine's in-memory fallback deduplicated before
applying the function, while driver-sql, driver-turso, driver-mongodb,
driver-memory and the analytics SQL builder all ignored it, so
{ function: 'sum', field: 'amount', distinct: true } answered a deduplicated sum on
the fallback path and an ordinary sum on every SQL datasource — one query, two
plausible numbers, chosen by which backend served it. The live deduplicating spelling
is the count_distinct function (COUNT(DISTINCT field) on both SQL faces since
#6409); SUM(DISTINCT …) / AVG(DISTINCT …) have no replacement, because no backend
ever computed them here.
// SortNode — ORDER BY element
interface SortNode {
field: string;
order: 'asc' | 'desc'; // default: 'asc'
}
// AggregationNode — aggregation definition
interface AggregationNode {
function: 'count' | 'sum' | 'avg' | 'min' | 'max' | 'count_distinct';
field?: string; // optional for COUNT(*)
alias: string; // result column alias
filter?: FilterCondition; // [EXPERIMENTAL — not enforced] FILTER WHERE clause — never applied
}
// `distinct?: boolean` was REMOVED in protocol 17 (#6815) — see the callout above.
// FieldNode — one entry of the select list. One of the queried object's OWN
// column names. The type is `string`, so a dotted path ('owner.name') still
// PARSES, but it resolves nothing: no driver ever implemented dotted
// projection, and the ingress refuses it (400 INVALID_FIELD, #7532). Related
// data — whole records and single related columns alike — comes from `expand`,
// not from inside this list.
//
// The `{ field, fields, alias }` nested-select member this union used to carry
// was REMOVED in protocol 17 (#4196): nothing produced it and nothing read
// `.fields`/`.alias`, so it was dropped by the SQL and memory drivers,
// projected as a column named "[object Object]" by MongoDB, and refused as an
// unknown field by the REST ingress. `expand` is the one spelling.
type FieldNode = string;
// GroupByNode — GROUP BY target
type GroupByNode = string | {
field: string;
dateGranularity?: 'day' | 'week' | 'month' | 'quarter' | 'year';
alias?: string; // defaults to `field`
};const customers = await engine.find('customer');
// SQL: SELECT * FROM customer;
// MongoDB: db.customer.find({})const customers = await engine.find('customer', {
fields: ['company_name', 'industry', 'annual_revenue'],
});
// SQL: SELECT company_name, industry, annual_revenue FROM customer;
// MongoDB: db.customer.find({}, { company_name: 1, industry: 1, annual_revenue: 1 })const customers = await engine.find('customer', {
limit: 10,
offset: 20, // Skip first 20, get next 10
});
// SQL: SELECT * FROM customer LIMIT 10 OFFSET 20;
// MongoDB: db.customer.find().skip(20).limit(10)Filters use the where clause with MongoDB-style $op operators (object syntax).
The simplest filter — a field-value pair implies $eq:
const query: QueryAST = {
object: 'customer',
where: {
industry: 'tech', // Implicit: { $eq: 'tech' }
},
};
// SQL: WHERE industry = 'tech'Use $op keys for non-equality comparisons:
// Not equal
const query: QueryAST = {
object: 'customer',
where: {
status: { $ne: 'inactive' },
},
};
// SQL: WHERE status != 'inactive'
// Greater than
const query: QueryAST = {
object: 'customer',
where: {
annual_revenue: { $gt: 1000000 },
},
};
// SQL: WHERE annual_revenue > 1000000| Operator | Description | Example |
|---|---|---|
$eq |
Equal (implicit default) | { status: 'active' } or { status: { $eq: 'active' } } |
$ne |
Not equal | { status: { $ne: 'closed' } } |
$gt |
Greater than | { revenue: { $gt: 10000 } } |
$gte |
Greater or equal | { score: { $gte: 80 } } |
$lt |
Less than | { age: { $lt: 65 } } |
$lte |
Less or equal | { discount: { $lte: 20 } } |
$in |
In list | { stage: { $in: ['proposal', 'negotiation'] } } |
$nin |
Not in list | { status: { $nin: ['deleted', 'archived'] } } |
$contains |
String contains, case-sensitive | { name: { $contains: 'Inc' } } |
$icontains |
String contains, ignoring ASCII case | { name: { $icontains: 'inc' } } |
$notContains |
String does not contain, case-sensitive | { name: { $notContains: 'test' } } |
$startsWith |
String starts with, case-sensitive | { email: { $startsWith: 'admin' } } |
$endsWith |
String ends with, case-sensitive | { domain: { $endsWith: '.com' } } |
$like |
LIKE pattern — you write the wildcards, case-sensitive |
{ name: { $like: '%Industries' } } |
$ilike |
Same pattern language, ignoring ASCII case | { name: { $ilike: '%industries' } } |
$between |
Range (inclusive) | { close_date: { $between: ['2024-01-01', '2024-12-31'] } } |
$null |
Null check | { manager_id: { $null: true } } / { phone: { $null: false } } |
$exists |
Field has a value — the inverse of $null |
{ metadata: { $exists: true } } |
The two take different things and picking the wrong one is a silently wrong answer rather than an error:
$containstakes TEXT. It is matched literally as a substring, and%,_and regex metacharacters in your comparand are ordinary characters — escaped on your behalf.$liketakes a PATTERN.%matches any sequence,_matches exactly one character, and a backslash escapes either. The pattern is matched against the whole value, so a pattern with no wildcards is an exact comparison, not a substring search.
{ name: { $contains: 'Industries' } } // Acme Industries, Industries Ltd, Industries
{ name: { $like: 'Industries' } } // Industries — exact
{ name: { $like: '%Industries' } } // Acme Industries, Industries — ends withA pattern ending in a lone unpaired backslash is refused (INVALID_FILTER): no
reading of it survives every backend, so it is rejected rather than guessed.
The string operators compare case-sensitively. $icontains is the one that does
not, and the case it ignores is ASCII case only — A-Z against a-z, and nothing
else.
// Case-sensitive: matches "acme corp", NOT "ACME Corp"
{ name: { $contains: 'acme' } }
// ASCII case-insensitive: matches BOTH "acme corp" and "ACME Corp"
{ name: { $icontains: 'acme' } }The boundary is ASCII because that is the only fold every backend can actually
deliver. SQLite compiled without ICU — which is what driver-sqlite-wasm and
driver-turso run on — folds ASCII only in both LOWER() and LIKE, so a
Unicode promise here would be a guarantee three of the five backends could not
keep. See #4706.
The comparand is always matched literally. % and _ are ordinary characters,
not LIKE wildcards, and . / * / + are ordinary characters, not regex
metacharacters — { name: { $icontains: 'a.b' } } matches a.b and not axb.
The other half of the case rules above has landed too: $contains /
$startsWith / $endsWith / $notContains are case-sensitive by ruling and
are so on every backend —
#6682 removed the
hardcoded $options: 'i' that folded them on MongoDB and the case-insensitive
regex that folded them on the in-memory driver's query and analytics faces.
If you were relying on $contains being loose on the in-memory driver, that
is a row-set change: write $icontains when you want a fold. The shared
standard both halves are measured against is FILTER_TEXT_CASES
(@objectstack/spec/data), which every driver now runs.
$regex (and its $options companion) was never a declared operator and is
retired (#4706). It
could not mean one thing across the backends: driver-sql compiled it to a
LIKE-escaped substring match, so a.b matched only the literal a.b, while
driver-memory evaluated it as a real RegExp, so the same filter also matched
axb — and an invalid pattern was caught and answered zero rows, in silence. A real
regex is not implementable on all five backends: driver-turso's remote transport
speaks a wire protocol with no way to register a SQLite REGEXP function.
| Instead of | Write |
|---|---|
{ name: { $regex: 'acme' } } |
{ name: { $icontains: 'acme' } } |
{ name: { $regex: 'acme', $options: 'i' } } |
{ name: { $icontains: 'acme' } } |
{ name: { $regex: '^acme' } } |
{ name: { $startsWith: 'acme' } } |
{ name: { $regex: 'acme$' } } |
{ name: { $endsWith: 'acme' } } |
A pattern that genuinely needs a regular expression has no filter-level
replacement — narrow the query with the declared operators and match in application
code. The prescriptions above are declared as data in RETIRED_FILTER_OPERATORS
(@objectstack/spec/data), so every backend's refusal quotes the same sentence.
Multiple keys in where are combined with AND logic:
const query: QueryAST = {
object: 'opportunity',
where: {
stage: 'Closed Won',
amount: { $gt: 50000 },
close_date: { $gte: '2024-01-01' },
},
};
// SQL: WHERE stage = 'Closed Won' AND amount > 50000 AND close_date >= '2024-01-01'const query: QueryAST = {
object: 'contact',
where: {
$or: [
{ title: { $contains: 'CEO' } },
{ title: { $contains: 'President' } },
{ title: { $contains: 'Founder' } },
],
},
};
// SQL: WHERE (title LIKE '%CEO%' OR title LIKE '%President%' OR title LIKE '%Founder%')Explicit $and is useful when you need multiple conditions on the same field:
const query: QueryAST = {
object: 'product',
where: {
$and: [
{ price: { $gte: 10 } },
{ price: { $lte: 100 } },
],
},
};
// SQL: WHERE price >= 10 AND price <= 100const query: QueryAST = {
object: 'customer',
where: {
$not: {
status: { $in: ['deleted', 'suspended'] },
},
},
};
// SQL: WHERE NOT (status IN ('deleted', 'suspended'))const query: QueryAST = {
object: 'opportunity',
where: {
type: 'new_business', // AND (type = new_business)
$or: [ // AND (
{ amount: { $gt: 100000 } }, // amount > 100000
{ is_strategic: true }, // OR is_strategic = true
], // )
},
};
// SQL: WHERE type = 'new_business'
// AND (amount > 100000 OR is_strategic = true)A comparand can be a field reference instead of a literal — { $field: 'other_column' }
— as the whole comparand of one of the six scalar comparison operators
($eq / $ne / $gt / $gte / $lt / $lte). Both execution paths answer it: the
in-memory evaluator (matchesFilter, @objectstack/formula) resolves the reference
against the record, and SQL push-down (driver-sql, driver-sqlite-wasm) compiles it to
a same-table column-to-column comparison written total across NULLs, so the two return
the same rows. A reference is not allowed as an $in / $nin member or a $between
endpoint — the schema refuses those positions by name.
{/* os:check */}
import type { FilterCondition } from '@objectstack/spec/data';
// completed_at <= due_date
const onTime: FilterCondition = {
completed_at: { $lte: { $field: 'due_date' } },
};
// completed_at <= due_date + grace_days (grace_days is a numeric column)
const onTimeWithGrace: FilterCondition = {
completed_at: { $lte: { $field: 'due_date', addDays: { $field: 'grace_days' } } },
};
// completed_at > due_date + 5 (a literal binds where the column would)
const lateByMoreThanFive: FilterCondition = {
completed_at: { $gt: { $field: 'due_date', addDays: 5 } },
};
// completed_at >= due_date - 3 (a negative integer subtracts; there is no subDays)
const withinThreeDaysBefore: FilterCondition = {
completed_at: { $gte: { $field: 'due_date', addDays: -3 } },
};addDays adds a whole-day offset to the referenced column before the comparison:
an integer literal of any sign, or a nested { $field } reference to a numeric column
holding the number of days. Whole days are the only unit. The NULL semantics are
stated rather than inherited from SQL three-valued logic:
| Case | Reads as |
|---|---|
| The offset column is NULL | zero days — due_date + NULL is due_date |
| The referenced column is NULL | the comparison is false, for every operator ($ne included) — no deadline is never "on time", so $not re-admits the row |
| The target column is NULL | its ordinary reading — fails the orderings and $eq, satisfies $ne when the offset deadline exists |
On SQL push-down the offset compiles only between two temporal columns of the same
class (date with date, datetime with datetime) against a numeric offset column,
on every dialect the $field compiler covers (SQLite, PostgreSQL, MySQL); the
memory evaluator matches, and a fractional offset value is truncated toward zero on
both. The same-table rule applies to the offset column too, and the offset is held to it
at save time: addDays: { $field: 'duty.grace_days' } (a relation path) is refused with
INVALID_FILTER by os validate, by the filter and dataset save doors and by every read,
in the SQL push-down's own words. Only the referenced $field itself keeps the older
asymmetry — resolved by the memory evaluator, refused by SQL push-down.
A dataset's include does not widen this. A dataset compiles a LEFT JOIN for every
relation it includes (ADR-0071, up to three hops), and on the native-SQL path those joins
serve dimensions, measures and filter members — a where on duty.grace_days reads the
joined column. A { $field } comparand takes a different road: the native-SQL strategy
declines it so that the driver, which alone holds the declared field set, the column types
and the tenant column the cross-field rules check, compiles it (#7598) — and the driver
compiles single-table statements, with no join in scope. So
completed_on <= due_on + duty.grace_days is refused with INVALID_FILTER even when duty
is in the dataset's include — at save time now, and by the driver for a dataset that
never met the save door; measured and pinned in
packages/services/service-analytics/src/__tests__/include-relation-cross-field-boundary.test.ts
(#15103). The spelling that answers on both paths is the same-table one — every column the
reference names, offset included, is a column of the object the filter runs over.
A comparand can be anchored at the current instant — { $now: true, addDays: <offset> } —
as the whole comparand of one of the six scalar comparison operators, on a date or
datetime column. addDays is required and takes the same two spellings the field
reference's does: an integer literal of any sign, or a nested { $field } reference to a
numeric column of the same record. There is no subDays: a column that means "days
before now" holds negative values. For the unshifted instant, write the {now} placeholder.
{/* os:check */}
import type { FilterCondition } from '@objectstack/spec/data';
// opened more than review_sla_days_offset days ago — the column holds NEGATIVE days
const pastSla: FilterCondition = {
opened_at: { $lt: { $now: true, addDays: { $field: 'review_sla_days_offset' } } },
};
// closed on or after thirty days ago
const recentlyClosed: FilterCondition = {
closed_on: { $gte: { $now: true, addDays: -30 } },
};
// due within the next seven days (and not yet past)
const dueSoon: FilterCondition = {
$and: [
{ due_on: { $gte: { $now: true, addDays: 0 } } },
{ due_on: { $lte: { $now: true, addDays: 7 } } },
],
};The engine's token resolver pins $now to the one instant every {N_days_ago} placeholder
in the same query resolves from, so a driver sees { $now: '<ISO instant>', addDays }; a
caller that reaches a driver or the memory evaluator directly, with no resolver in front of
it, gets that executor's own clock. The target column's class decides the form the instant
is compared in, exactly as a {now} literal is: a datetime column keeps the time of day;
a date column meets the instant's UTC calendar day (the time of day is dropped, never
rounded). The anchor itself is never NULL, so the only NULL on the table is the target's:
| Case | Reads as |
|---|---|
| The offset column is NULL | zero days — now + NULL is now |
| The target column is NULL | fails the orderings and $eq, satisfies $ne — its ordinary reading against a literal, so $not takes exactly the complement |
The target column is not date / datetime |
refused with INVALID_FILTER — an instant has no meaning on a number, a wall-clock time or text |
Both execution paths answer it: SQL push-down (driver-sql, driver-sqlite-wasm) binds the
instant in the column's storage form and shifts it with the same per-dialect day arithmetic
the addDays pair uses (SQLite, PostgreSQL, MySQL), and the in-memory evaluator shifts it in
the form of the stored value it meets, so the two return the same rows. The offset column is
held to every rule the addDays pair's offset is held to — a declared, numeric column of the
same table, never the tenant-isolation column, no dotted path — and the analytics native-SQL
strategy declines a query carrying an anchor so that the driver compiles it, as it does for a
{ $field } comparand. The MongoDB driver refuses the anchor rather than translating it.
The SLA held on a related record. The shape that compiles is the same-table one: the SLA
(or its negative) propagated onto the row, or the dataset's base object carrying the column.
A dotted offset (addDays: { $field: 'queue.sla_days' }) is refused with INVALID_FILTER
when the filter, dataset or stack is saved or validated, in the SQL push-down's words — a
dataset's include does not widen it (see above). The memory evaluator would walk the path,
but on a stored row the relation holds the related record's id, so the offset would read as
zero days: copy the related value onto a numeric column of the row instead.
Before a comparison is built, the driver puts the comparand into the same canonical
form the column is stored in (SqlDriver.coerceFilterValue) — the identical function
the write path uses, on every dialect, so the two sides of a comparison can never be
decided by their shapes disagreeing:
| Field type | Canonical form | Meaning |
|---|---|---|
date |
YYYY-MM-DD |
Timezone-naive calendar day |
datetime |
YYYY-MM-DDTHH:MM:SS.sssZ |
A UTC instant |
time |
HH:MM:SS — .fff only when the milliseconds are non-zero |
Timezone-naive wall-clock time of day |
{/* os:check */}
import type { FilterCondition } from '@objectstack/spec/data';
// `date` field — a bare calendar day matches that day
const onDay: FilterCondition = { close_date: '2024-01-15' };
// `date` range — $between is inclusive on both ends
const inYear: FilterCondition = {
close_date: { $between: ['2024-01-01', '2024-12-31'] },
};
// `datetime` field — a bare `YYYY-MM-DD` is completed to midnight UTC
// (`2024-01-15T00:00:00.000Z`), so this is an exact-instant match, not "that day"
const atMidnight: FilterCondition = { created_at: '2024-01-15' };
// A whole UTC day on a `datetime` needs a half-open range
const duringDay: FilterCondition = {
created_at: { $gte: '2024-01-15', $lt: '2024-01-16' },
};
// `time` field — `'09:00'` is completed to `'09:00:00'`
const businessHours: FilterCondition = {
start_time: { $gte: '09:00', $lte: '18:00' },
};A bare YYYY-MM-DD bound is a calendar day. As a lower bound ($gte) it
means the start of that day (midnight UTC); as an upper bound ($lte, or the
max of a $between) it covers the whole day. On a date column that is
plain comparison, so the $between above includes Dec 31. On a datetime
column it is the shared filter lowering (lowerFilterCondition from
@objectstack/spec/data, ADR-0053 D-D1 as amended): the engine's filter seams
(where, and on aggregate the per-aggregation filter and having) and the
RLS compile seam rewrite a bare-day upper bound on a datetime column
half-open ($lt the next day) before any driver or in-memory evaluator sees the
filter, and a seam that cannot read the declared types applies that rewrite to
every column. A filter handed directly to a driver past those seams —
SqlDriver or a driver built on it (SqliteWasmDriver, TursoDriver),
InMemoryDriver or MongoDBDriver — or to applyInMemoryAggregation is
compared as written: a bare-day $lte on a datetime column compares against
that day's midnight. A full ISO timestamp keeps exact-instant semantics on every
operator.
{/* os:check */}
// Field IS NULL
where: { manager_id: { $null: true } }
// Field IS NOT NULL
where: { phone: { $null: false } }
// Field has a value — the same rows as { $null: false }
where: { metadata: { $exists: true } }Filter on a related record's fields by nesting the condition beneath the relation
field in where:
// Opportunities whose account is in the tech industry
const opportunities = await engine.find('opportunity', {
where: { account: { industry: 'tech' } },
});A condition on a related record's fields beneath a relation field (lookup,
master_detail, user, tree) is served in where: the engine reads the
related object with it as the caller, then matches the field against the
ids it returns ($in; any member when multiple: true).
Limits — one level: every key a field the related object declares, no relation
or dotted key inside; forward only: never a parent by its children; where
only: an aggregation's filter and having refuse it, INVALID_FILTER / 400;
at most 1000 related ids, refused past that, INVALID_FILTER / 400, never
truncated; as the caller: the related object's row scope and field permissions
apply, so a field the caller cannot read is refused, PERMISSION_DENIED / 403,
never an empty result.
- Every verb that takes a
whereserves it —find,findOne,count,aggregate,updateanddelete— and so do the RESTGET /data/:objectandPOST /data/:object/querydoors, which read through the same engine calls. A single-valued relation matches with$inon the related ids; a multi-valued one (multiple: true) matches a record when any member qualifies, written as one$containsper id under an$or. - A second level is refused.
{ account: { owner: { region: 'NA' } } }and{ account: { 'owner.region': 'NA' } }answerINVALID_FILTER/ 400. - The reverse direction is not served. A parent filtered by its children is not
written as
{ opportunities: { stage: 'won' } }: the parent has no such field, so the REST doors answerINVALID_FIELD/ 400. - A dotted path is still refused.
where: { 'account.industry': 'tech' }answersINVALID_FIELD/ 400 at the engine and at the REST doors; the message names the nested spelling to write instead.
Past the 1000-id cap, and for the reverse direction, run the two steps yourself —
filter the related object, then $in its ids:
// Past the cap: read the related ids, then match the local key
const techAccounts = await engine.find('account', {
where: { industry: 'tech', annual_revenue: { $gt: 1000000 } },
fields: ['id'],
});
const opportunities = await engine.find('opportunity', {
where: { account: { $in: techAccounts.map((a) => a.id) } },
});
// Reverse: accounts that have a won opportunity
const won = await engine.find('opportunity', {
where: { stage: 'won' },
fields: ['account'],
});
const accounts = await engine.find('account', {
where: { id: { $in: won.map((o) => o.account) } },
});On a multiple: true relation, match the ids with one $contains per id under an
$or instead of $in.
Since #8296 both doors refuse it with 400 INVALID_FIELD — the REST/protocol
ingress and engine.find() alike:
Query parameter 'where' filters on 'is_open', a virtual 'formula' field on object
'showcase_task'. Its value is computed on read and never stored, so no driver
materializes a column to filter on: the predicate reaches the driver, matches
nothing, and the query answers an empty list under a 200 — in BOTH directions, so a
false test returns no records where the same test against a stored boolean returns
every record. Denormalise the value onto 'showcase_task' (a stored field, written
when the source changes) and filter that.
INVALID_FIELD, not INVALID_FILTER: the verdict is about the name's type, not
the value's shape. The remedy is the same one the sort axis prescribes — denormalise
the value onto the queried object as a stored field, one this object's own rows
carry, written when the source changes, and filter that.
summary and autonumber are not refused: both get real, stored columns and
filter correctly. A dotted filter path has no verdict on this axis — it is judged by
the relation rule above.
Sorting uses the orderBy array of SortNode objects.
const query: QueryAST = {
object: 'customer',
orderBy: [{ field: 'company_name', order: 'asc' }],
};
// SQL: ORDER BY company_name ASCconst query: QueryAST = {
object: 'opportunity',
orderBy: [
{ field: 'priority', order: 'desc' },
{ field: 'created_at', order: 'asc' },
],
};
// SQL: ORDER BY priority DESC, created_at ASCInternal callers reaching engine.find() directly are unaffected: a dotted
orderBy there still falls through to the driver backstop and orders nothing.
A rollup/summary field does get a real, maintained column and can be
sorted on — but it aggregates child records (count/sum/min/max/
avg), so it cannot carry a looked-up parent's column such as
account.company_name. For that, write the value onto a stored field of the
queried object and keep it in sync (a trigger or flow on the source record).
This page taught the formula/rollup version until #6924, and so did the
400 INVALID_SORT hint itself (#4256) — both are corrected together. The same
correction on the search axis is #6673.
The expand property enables recursive loading of related records through the reference field types — lookup, master_detail, user and tree (REFERENCE_VALUE_TYPES). Each key is a relationship field name; the value is a nested QueryAST. Over the REST/protocol ingress, a key that is not one of those is 400 INVALID_FIELD rather than a silently absent relation.
const query: QueryAST = {
object: 'opportunity',
fields: ['name', 'amount'],
expand: {
account: {
object: 'account',
fields: ['company_name'],
},
},
};
// Result:
// [
// {
// name: 'Big Deal',
// amount: 100000,
// account: { company_name: 'Acme Corp' }
// }
// ]const query: QueryAST = {
object: 'opportunity',
fields: ['name'],
expand: {
account: { object: 'account', fields: ['company_name'] },
owner: { object: 'user', fields: ['name', 'email'] },
},
};const query: QueryAST = {
object: 'task',
fields: ['title', 'assignee'],
expand: {
assignee: { object: 'user', fields: ['name', 'email'] },
project: {
object: 'project',
expand: {
org: { object: 'org', fields: ['name'] },
},
},
},
};Expansion follows the same reference field types as above — lookup,
master_detail, user and tree — i.e. the foreign key lives on the object
you are querying. The nested QueryAST can filter (where) and select
(fields) the related records:
const query: QueryAST = {
object: 'task',
fields: ['title', 'assignee'],
expand: {
// assignee is a lookup → user; only resolve assignees that are still active.
assignee: {
object: 'user',
where: { active: { $eq: true } },
fields: ['name', 'email'],
},
},
};The nested where is AND-merged with the batch $in the engine uses to load related
records, so a related record is attached only when it also matches your filter. A foreign
key whose target is filtered out is left as the raw id (unresolved) rather than dropped.
The nested fields is a projection of the related record, and you do not have to name
id in it to make the expansion work. id is the join key the batch $in re-attaches by,
so the engine adds it to its own sub-read and strips it back out when you did not ask for
it — the attached record carries exactly the columns you listed. Name id explicitly when
you want it (e.g. to link to the related record).
Aggregations use the aggregations array with AggregationNode objects, combined with groupBy for grouping.
const count = await engine.count('customer', {
where: { industry: 'tech' },
});
// SQL: SELECT COUNT(*) FROM customer WHERE industry = 'tech'
// Result: 42const query: QueryAST = {
object: 'opportunity',
fields: ['stage'],
groupBy: ['stage'],
aggregations: [
{ function: 'count', alias: 'count' },
{ function: 'sum', field: 'amount', alias: 'total_amount' },
],
};
// Result:
// [
// { stage: 'Prospecting', count: 10, total_amount: 500000 },
// { stage: 'Qualification', count: 5, total_amount: 250000 }
// ]SQL compilation:
SELECT
stage,
COUNT(*) AS count,
SUM(amount) AS total_amount
FROM opportunity
GROUP BY stageconst query: QueryAST = {
object: 'opportunity',
aggregations: [
{ function: 'count', alias: 'count' },
{ function: 'sum', field: 'amount', alias: 'total' },
{ function: 'avg', field: 'amount', alias: 'average' },
{ function: 'min', field: 'amount', alias: 'min_amount' },
{ function: 'max', field: 'amount', alias: 'max_amount' },
],
};
// Result:
// { count: 100, total: 5000000, average: 50000, min_amount: 10000, max_amount: 500000 }Schema enum: count, sum, avg, min, max, count_distinct.
const query: QueryAST = {
object: 'opportunity',
fields: ['stage', 'owner_name'],
groupBy: ['stage', 'owner_name'],
aggregations: [
{ function: 'count', alias: 'count' },
{ function: 'sum', field: 'amount', alias: 'total' },
],
};Enforced since #4286 (ADR-0049, resolved to enforce). The engine applies having
itself, AFTER aggregation, identically on the native-driver path and the in-memory
fallback (packages/objectql/src/having-filter.ts) — the same correct-first /
optimize-later two-tier shape date bucketing uses; native SQL HAVING pushdown can come
later behind a driver capability flag without changing these semantics. The REST
findData() aggregate branch forwards the clause.
having references the aggregated row's own columns — aggregation aliases and
groupBy projections — with the ordinary FilterCondition operators and
$and / $or / $not. An unknown operator rejects the query loudly rather than being
ignored (an ignored operator would silently return unfiltered aggregates — the ADR-0078
failure mode enforcement exists to end).
// Only accounts with > $1M pipeline
const rows = await engine.aggregate('opportunity', {
groupBy: ['account_id'],
aggregations: [
{ function: 'sum', field: 'amount', alias: 'total' },
],
having: { total: { $gt: 1_000_000 } },
});A groupBy entry may be an object carrying dateGranularity, which buckets a
date/datetime column into uniform periods. The bucketed value is projected under
the field name (or alias, on the in-memory path):
const revenueByMonth = await engine.aggregate('order', {
where: { status: 'completed' },
groupBy: [{ field: 'created_at', dateGranularity: 'month' }],
aggregations: [
{ function: 'sum', field: 'total_amount', alias: 'revenue' },
],
});Granularities: day, week, month, quarter, year. The engine pushes the
bucket down to the driver only when it advertises native support for that granularity
(supports.queryDateGranularity); otherwise it falls back to in-memory bucketing over
the driver's raw rows.
query.distinct was removed in @objectstack/spec 17 (#4286): no driver ever
rendered SELECT DISTINCT, and the flag's only observable effect was mis-wired — it
silently suppressed the REST list count (total/hasMore degraded to a page-local
estimate) while still returning duplicate rows. The key is tombstoned and
QueryBuilder.distinct() was removed with it; the count suppression is gone, so
total is truthful again. Unique combinations come from groupBy, deduplicated
counts from the count_distinct aggregation, and one column's distinct values from
the driver's own distinct() method (implemented by the SQL and in-memory drivers;
it is not part of the IDataDriver contract):
const industries = await driver.distinct('account', 'industry');SqlDriver.distinct() presents each value exactly the way find() presents that
column — a date as YYYY-MM-DD and a time as HH:MM:SS[.fff] on every dialect, a
datetime folded to canonical UTC ISO on SQLite (the one dialect where storage differs
from presentation; Postgres and MySQL hand back their own native temporal value) — and
then re-deduplicates the presented values, because SQL DISTINCT compares the stored
form.
It is tenant-scoped on the same terms as find() — the organization_id predicate is
applied when the call carries options.tenantId (the fourth argument), and the example
above omits it, so it returns the column's values across every tenant. Until #6792 the
predicate was dropped even when tenantId was supplied, which made a scoped call
disclose every other tenant's values for that column.
The search parameter does not reach a full-text index. The engine expands it into
an $or of $icontains predicates across the object's server-resolved searchable fields
(ADR-0061) and deletes search from the AST before the driver sees it — every driver
already runs $or/$icontains, so no driver support is needed (which is also why the
old supports.fullTextSearch capability bit had no reader and was retired in 17.0.0,
#4634).
search takes the query text itself — that is the canonical spelling (ADR-0061 D1:
the client says what to search for, the server decides which fields), and it is what
every surface sends. The structured form is equivalent for the two members that drive
the expansion, and carries the experimental knobs below:
// Canonical — the server resolves the fields from object metadata
const query: QueryAST = {
object: 'article',
search: 'ObjectStack tutorial',
searchFields: ['title', 'content'], // optional narrowing
limit: 10,
};
// Structured form — `query` + `fields` mean exactly the same thing
const structured: QueryAST = {
object: 'article',
search: { query: 'ObjectStack tutorial', fields: ['title', 'content'] },
limit: 10,
};Field resolution is server-side and never client-trusted: the requested fields
(searchFields, or search.fields in the structured form) are intersected with the
object's declared searchableFields (or, absent those, an auto-default of the name field
plus short-text/enum fields), so naming a field outside that set can never widen the
search — and over the REST/protocol ingress it is 400 INVALID_FIELD outright (#4254),
because the engine-side intersection alone used to drop the unknown name and fall back to
scanning the full searchable set. Internal callers reaching engine.find() directly keep
the tolerant intersection, but not a search with nothing to scan: when no
searchableFields entry names a stored field and the auto-default finds none
(internal: true fields never count), the engine itself refuses a non-blank search
with 400 INVALID_FIELD (param: 'search') on find, findOne, count and
aggregate alike, rather than answering every row. Multiple whitespace-separated terms are AND-ed and
fields are OR-ed. Case sensitivity comes from the operator the expansion emits, not
from the expansion: it emits $icontains, which ignores ASCII case by the
rule in Case Sensitivity above. So a user typing acme does
find ACME Corp — search is case-insensitive by ruling
(#7641). select /
status option labels are matched case-insensitively too, by a separate
label→value mapping in the expansion rather than by the operator.
Search targets are this object's own columns. A dotted path is not one of
them: searchFields: ['project_id.name'] is refused at the ingress rather than
dropped, because the search axis does not resolve traversal the way fields,
sort and filters do:
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 declarative 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 carries a project_name text column on task,
maintained on write and listed in task.searchableFields, so the expansion stays
a single-table $or of $icontains. The mirror must be stored: a formula
field is virtual, no driver materializes a column for it, and an $icontains
against one has nothing to scan. Cross-object search paths are rejected by
design, not pending — see Schema Design → Searching by a related record's
title
for the maintenance hooks.
query.joins was removed in @objectstack/spec 17 (#4286, ADR-0049
enforce-or-remove): no driver ever read it, so a query carrying joins silently ran
as a single-table query. The key is tombstoned — authoring it is a tsc error, and a
query that still carries it (even as an empty array) fails to parse with the upgrade
prescription. The JoinNode / JoinType / JoinStrategy exports left with it.
Use expand (§4) for relationship loading — the live spelling for related records,
and for single related columns too, since its nested QueryAST both filters (where)
and selects (fields) the related record's columns. Otherwise, two queries joined in
application code.
A dotted fields path is not the alternative. 'owner.name' still parses —
FieldNode is string, a shape check — but no driver ever resolved one, and the
ingress refuses it with 400 INVALID_FIELD (#7532). Where the value is wanted on the
queried object itself, denormalise it onto that object (a stored field, written when
the source changes) — the same remedy the sort axis prescribes (#6924).
{ fields: ['title'], expand: { project_id: { object: 'project' } } }
// -> nothing to resolve; no related record comes back
{ fields: ['title', 'project_id'], expand: { project_id: { object: 'project' } } }
// -> worksquery.windowFunctions was removed in @objectstack/spec 17 (#4286): find()
never applied it, so every OVER clause it declared was silently dropped. The key is
tombstoned, and the WindowFunction / WindowSpec / WindowFunctionNode exports
left with it — they declared field / over / frame members that no executor ever
read.
Window functions remain a SQL-driver door: findWithWindowFunctions(), which is
not on the IDataDriver contract and is not surfaced by IDataEngine. Its input is
the driver's own flat shape — function name, alias, and optional flat
partitionBy / orderBy:
const ranked = await driver.findWithWindowFunctions('order', {
windowFunctions: [
{
function: 'row_number',
alias: 'rank',
partitionBy: ['customer_id'],
orderBy: [{ field: 'amount', order: 'desc' }],
},
],
});
// SQL: SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank
// FROM ordersThat method always selects * plus the window columns — it does not honor a fields
projection, and niladic rendering means argument-taking functions (LAG(field))
emit without their argument. For request-level analytics use aggregations +
groupBy (§5).
Tenancy works exactly as it does on find(): the driver applies its
organization_id predicate only when the call carries options.tenantId. The example
above omits it and therefore reads across every tenant — the intended unscoped/admin
path, but a deliberate choice rather than a default to inherit. (Until #6792 this door
dropped options.tenantId even when it was supplied, so a scoped call still returned
every tenant's rows.)
// Page 1 (records 0-9)
const page1 = await engine.find('customer', {
limit: 10,
offset: 0,
});
// Page 2 (records 10-19)
const page2 = await engine.find('customer', {
limit: 10,
offset: 10,
});Drawback: Slow for large offsets (database still scans all skipped rows).
Determinism: the walk above visits every customer exactly once even though
it names no orderBy. A driver orders any paged read by a unique column of its
own — appended to your sort keys, or standing alone when you gave none — because
an unordered LIMIT/OFFSET slices an arrangement nothing holds steady: SQL
leaves row order to the plan, and MongoDB's natural order moves when a document
does. Page 2 would otherwise repeat a row from page 1 and drop another, with
every page full and every row real (objectui#3106, #4363). A query with no
limit/offset is untouched — nothing is being sliced, so no order is imposed.
// First page
const page = await engine.find('customer', {
limit: 10,
orderBy: [{ field: 'created_at', order: 'asc' }],
});
// Next page — seek past the last row instead of offsetting.
// The comparand is canonicalised by the same function that wrote the column
// (`coerceFilterValue` → `storageDatetimeValue`), so the range compare is an
// ordinary indexable comparison on every dialect: canonical UTC ISO text on
// SQLite, `timestamptz` on Postgres, `DATETIME(3)` on MySQL.
const next = await engine.find('customer', {
where: { created_at: { $gt: page[page.length - 1].created_at } },
limit: 10,
orderBy: [{ field: 'created_at', order: 'asc' }],
});Advantage: Consistent performance regardless of page depth.
const openOpportunities = await engine.find('opportunity', {
where: {
stage: { $nin: ['Closed Won', 'Closed Lost'] },
owner_id: currentUser.id,
},
orderBy: [{ field: 'amount', order: 'desc' }],
fields: ['name', 'amount', 'close_date'],
expand: {
account: { object: 'account', fields: ['company_name'] },
},
});const products = await engine.find('product', {
where: {
is_active: true,
inventory_qty: { $gt: 0 },
category_id: { $in: selectedCategories },
price: { $between: [minPrice, maxPrice] },
},
search: {
query: searchTerm,
fields: ['name', 'description'],
},
orderBy: [{ field: 'popularity_score', order: 'desc' }],
limit: 20,
});month is not a column — bucket the created_at instant with dateGranularity. Note
that engine.aggregate() takes a closed option set with no orderBy or limit (the
full list is under EngineAggregateOptions in the
Data Engine reference), so sort the returned rows
yourself. It does carry a timezone for bucketing.
const monthlyRevenue = await engine.aggregate('order', {
where: {
status: 'completed',
// `created_at` is a datetime — a bare date is read as midnight UTC
created_at: { $gte: '2024-01-01' },
},
groupBy: [{ field: 'created_at', dateGranularity: 'month' }],
aggregations: [
{ function: 'sum', field: 'total_amount', alias: 'revenue' },
{ function: 'count', alias: 'order_count' },
{ function: 'avg', field: 'total_amount', alias: 'avg_order' },
],
});
const sorted = monthlyRevenue.sort((a, b) =>
String(a.created_at).localeCompare(String(b.created_at)),
);Unknown field names are not rejected by engine.find() — a projected field that
doesn't exist on the object is silently dropped (matching OData / SELECT *
tolerance), so a stale field reference never fails the whole query:
const rows = await engine.find('customer', {
fields: ['name', 'nonexistent'], // `nonexistent` is not on the schema
});
// Returns each row with `name`; `nonexistent` is omitted — no error thrown.Row-level scoping is applied by narrowing the query — the security middleware
AND-merges its read filter into where, so an over-broad filter returns fewer rows
rather than throwing. What throws is an operation the caller is not permitted to run at
all, or a predicate that references a field the caller cannot read:
try {
await engine.find('account', { where: { owner_id: currentUser.id } });
} catch (error) {
// PermissionDeniedError:
// [Security] Access denied: operation 'find' on object 'account' ...
// [Security] Access denied: query on 'account' references field(s) not
// readable by the caller: ...
}**Tuple / Array / 三元组 Syntax — UI Builder Input Only**
The tuple/array format (e.g. ['status', '=', 'active']) and the filters key are legacy input formats used by some UI-layer filter builders (FilterBuilder, ObjectUI). They are not the canonical protocol format.
Before entering the ObjectQL protocol or IDataEngine, tuple filters must be converted to the canonical where + $op object format using the parseFilterAST() utility from @objectstack/spec/data:
{/* os:check */}
import { parseFilterAST } from '@objectstack/spec/data';
// UI Builder output (tuple format)
const uiFilter = ['and', ['status', '=', 'active'], ['priority', '>', 3]];
// Convert to canonical format
const where = parseFilterAST(uiFilter);
// → { $and: [{ status: 'active' }, { priority: { $gt: 3 } }] }Similarly, the following legacy field names should not be used in new code:
| Legacy | Canonical | Notes |
|---|---|---|
filters (array of tuples) |
where (FilterCondition object) |
Use parseFilterAST() to convert |
sort |
orderBy |
Array of { field, order } objects |
aggregate |
aggregations |
Same AggregationNode[]; the SQL and MongoDB drivers read either key, but engine.aggregate() forwards only aggregations |
expand (string array) |
expand (Record) |
Map of field name → nested QueryAST |
skip |
offset |
Number |
select |
fields |
Array of FieldNode (field-name strings) |
populate |
expand |
Map of field name → nested QueryAST |