objectstack/content/docs/protocol/objectql/query-syntax.mdx at 2d52025e09fdc408f572a0f9526a48d6f8210cb2 · objectstack-ai/objectstack

title	Query Syntax
description	Database-agnostic query language with filtering, joins, 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).

Query Philosophy

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 $in read, 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)

Query Structure

The QueryAST (Canonical)

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
}

**The protocol shape is wider than what the data engine executes.**

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/plugins/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 18, 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).

Key Types

// 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' | 'array_agg' | 'string_agg';
  field?: string;        // optional for COUNT(*)
  alias: string;         // result column alias
  distinct?: boolean;    // DISTINCT before aggregation — in-memory path only
  filter?: FilterCondition; // [EXPERIMENTAL — not enforced] FILTER WHERE clause — never applied
}

// FieldNode — one entry of the select list. A field name, optionally dotted to
// reach through a relationship ('owner.name'). Related *records* come 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`
};

1. Basic Queries

Select All Records

const customers = await engine.find('customer');

// SQL: SELECT * FROM customer;
// MongoDB: db.customer.find({})

Select Specific Fields

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 })

Limit and Offset

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)

2. Filtering

Filters use the where clause with MongoDB-style $op operators (object syntax).

Implicit Equality

The simplest filter — a field-value pair implies $eq:

const query: QueryAST = {
  object: 'customer',
  where: {
    industry: 'tech',       // Implicit: { $eq: 'tech' }
  },
};

// SQL: WHERE industry = 'tech'

Explicit Operators

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

Supported Operators

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	`{ name: { $contains: 'Inc' } }`
`$notContains`	String does not contain	`{ name: { $notContains: 'test' } }`
`$startsWith`	String starts with	`{ email: { $startsWith: 'admin' } }`
`$endsWith`	String ends with	`{ domain: { $endsWith: '.com' } }`
`$between`	Range (inclusive)	`{ close_date: { $between: ['2024-01-01', '2024-12-31'] } }`
`$null`	Null check	`{ manager_id: { $null: true } }` / `{ phone: { $null: false } }`
`$exists`	Field exists (NoSQL)	`{ metadata: { $exists: true } }`

Multiple Conditions (Implicit AND)

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'

Logical OR (`$or`)

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%')

Logical AND (`$and`)

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 <= 100

Logical NOT (`$not`)

const query: QueryAST = {
  object: 'customer',
  where: {
    $not: {
      status: { $in: ['deleted', 'suspended'] },
    },
  },
};

// SQL: WHERE NOT (status IN ('deleted', 'suspended'))

Complex Logic (AND + OR)

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)

Date, Datetime, and Time Filters

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 datetime column the driver compiles it half-open (< next day), so the $between above includes everything that happened on Dec 31. A full ISO timestamp keeps exact-instant semantics on every operator.

Null Checks

{/* os:check */}

// Field IS NULL
where: { manager_id: { $null: true } }

// Field IS NOT NULL
where: { phone: { $null: false } }

// Field exists (NoSQL)
where: { metadata: { $exists: true } }

Filtering Across Relationships

**Relation traversal inside `where` is not supported.** Neither the nested form (`where: { account: { industry: 'tech' } }`) nor a dotted path (`where: { 'account.industry': 'tech' }`) is resolved. `SqlDriver.applyFilters()` only recognises a nested object as an operator map when its keys start with `$`; anything else is compiled as a comparison against a single column of the queried table, and a dotted key is emitted verbatim, so Knex renders it as `"account"."industry"` against a table that was never joined.

Filter on the local foreign key, or run two queries:

const techAccounts = await engine.find('account', {
  where: { industry: 'tech', annual_revenue: { $gt: 1000000 } },
  fields: ['id'],
});

const opportunities = await engine.find('opportunity', {
  where: { account_id: { $in: techAccounts.map((a) => a.id) } },
});

3. Sorting

Sorting uses the orderBy array of SortNode objects.

Over the REST/protocol ingress, `orderBy` also accepts `'-created_at'`, `['-created_at']` and `{created_at: 'desc'}` — all normalized to the `SortNode[]` above. A sort naming a field the object does not have is `400 INVALID_SORT` there, rather than being dropped. Internal callers reaching `engine.find()` directly are unaffected.

Single Field Sort

const query: QueryAST = {
  object: 'customer',
  orderBy: [{ field: 'company_name', order: 'asc' }],
};

// SQL: ORDER BY company_name ASC

Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'created_at', order: 'asc' },
  ],
};

// SQL: ORDER BY priority DESC, created_at ASC

Sorting on Related Fields

`orderBy` only reaches columns of the queried table. Over the REST/protocol ingress a dotted path (`account.company_name`) is `400 INVALID_SORT` (#4256): no driver can order by it — `SqlDriver` would render it as `"account"."company_name"` against a table that was never joined, and until the path was refused, the unknown-column backstop retried **without the sort** and answered 200 with unordered rows. Denormalise the value onto the queried object (for example with a formula or rollup field) when you need to sort by it.

Internal callers reaching engine.find() directly are unaffected: a dotted orderBy there still falls through to the driver backstop and orders nothing.

4. Relationships (Expand)

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.

Basic Expand

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' }
//   }
// ]

Multiple Relationships

const query: QueryAST = {
  object: 'opportunity',
  fields: ['name'],
  expand: {
    account: { object: 'account', fields: ['company_name'] },
    owner: { object: 'user', fields: ['name', 'email'] },
  },
};

Nested Expand (Deep Loading)

const query: QueryAST = {
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      expand: {
        org: { object: 'org', fields: ['name'] },
      },
    },
  },
};

The engine resolves `expand` via batch `$in` queries (driver-agnostic) with a default max depth of 3.

Filtered Expand

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.

**Per-parent shaping (`limit` / `offset` / `orderBy`) is not honored on the expand path.** The engine batch-loads every parent's related records in a single `$in` query and then re-attaches them to each parent by foreign key, so it cannot express a per-parent page size, and the injected order follows each parent's own foreign-key value rather than the nested `orderBy`. To paginate or order related records, query the related object directly with its own `where` + `orderBy` + `limit`.

5. Aggregations

Aggregations use the aggregations array with AggregationNode objects, combined with groupBy for grouping.

Count

const count = await engine.count('customer', {
  where: { industry: 'tech' },
});

// SQL: SELECT COUNT(*) FROM customer WHERE industry = 'tech'
// Result: 42

Group By with Aggregations

const 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 stage

Over the REST/protocol ingress, both axes are validated before any driver runs: a `groupBy` entry or `aggregations[].field` naming a field the object does not have is `400 INVALID_FIELD` (the in-memory fallback used to collapse every row into one `null`-keyed bucket, and to answer `sum()` with `0`), and a shape the spec cannot read — a non-array, an entry naming no field, an unknown function or `dateGranularity`, a missing `alias` — is `400 INVALID_QUERY`. `count` with no `field` (or `field: '*'`) is the one legitimate field-less form. Internal callers reaching `engine.aggregate()` directly are unaffected.

Aggregation Functions

const 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, array_agg, string_agg.

Only `count`, `sum`, `avg`, `min`, and `max` are portable. `SqlDriver.mapAggregateFunc()` throws `Unsupported aggregate function: ` for `count_distinct`, `array_agg`, and `string_agg`; those three are implemented by the MongoDB driver and by the engine's in-memory aggregation fallback, but not by the SQL drivers.

Group By Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  fields: ['stage', 'owner_name'],
  groupBy: ['stage', 'owner_name'],
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total' },
  ],
};

HAVING Clause

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 } },
});

Date Bucketing

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.

6. Advanced Queries

Distinct

query.distinct was removed in @objectstack/spec 18 (#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.

Full-Text Search

The search parameter does not reach a full-text index. The engine expands it into an $or of $contains 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/$contains, so no driver support is needed (SqlDriver reports supports.fullTextSearch: false).

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. Multiple whitespace-separated terms are AND-ed and fields are OR-ed. Case sensitivity is the driver's, not the expansion's: the expansion emits a plain $contains, which SqlDriver compiles to a parameterised LIKE '%…%' with no case folding — so the dialect's own LIKE/collation rules decide — while the in-memory driver matches with a case-insensitive regex. Only select / status option labels are matched case-insensitively by the expansion itself. fuzzy, boost, operator, minScore, language, and highlight carry [EXPERIMENTAL — not enforced] markers (#4286): the schema accepts them, the expansion ignores them.

Joins — removed (#4286)

query.joins was removed in @objectstack/spec 18 (#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 — a dotted fields path ('owner.name') for a single related column, or two queries joined in application code.

Window Functions — removed from the request surface (#4286)

query.windowFunctions was removed in @objectstack/spec 18 (#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 orders

That 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).

7. Pagination

Offset-Based Pagination

// 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).

Keyset Pagination

`query.cursor` was **removed in `@objectstack/spec` 18** (#4286): no driver ever implemented keyset pagination, so a cursor was accepted and ignored and every page came back identical — a caller looping "until `hasMore` is false" never terminated. The key is tombstoned (on `EngineQueryOptions` too) and `QueryBuilder.cursor()` was removed with it. Express the keyset as an ordinary `where` predicate on the sort key — the pattern below is the supported one; a first-class cursor, if ever designed, will be a response-minted opaque token:

// 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.

8. Real-World Examples

CRM: Open Opportunities

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'] },
  },
});

E-Commerce: Product Search

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,
});

Analytics: Revenue by Month

month is not a column — bucket the created_at instant with dateGranularity. Note that engine.aggregate() accepts only where / groupBy / aggregations (plus a timezone for bucketing): there is no orderBy or limit on this path, so sort the returned rows yourself.

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)),
);

9. Error Handling

Unknown Fields Are Tolerated

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.

This tolerance does **not** extend to the REST/protocol ingress: a `select` / `fields` naming a column the object does not have is `400 INVALID_FIELD` there, because dropping it silently answered a *narrower* projection with a *wider* one — a projection left with no known field falls all the way back to every field. The engine's tolerance guards internal callers (hooks, flows, expand sub-reads, registry-less hosts) that never pass through that ingress.

Security Violations

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: ...
}

Legacy Compatibility

**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

Uh oh!

FilesExpand file tree

query-syntax.mdx

Latest commit

History

query-syntax.mdx

File metadata and controls

Query Philosophy

Query Structure

The QueryAST (Canonical)

Key Types

1. Basic Queries

Select All Records

Select Specific Fields

Limit and Offset

2. Filtering

Implicit Equality

Explicit Operators

Supported Operators

Multiple Conditions (Implicit AND)

Logical OR ($or)

Logical AND ($and)

Logical NOT ($not)

Complex Logic (AND + OR)

Date, Datetime, and Time Filters

Null Checks

Filtering Across Relationships

3. Sorting

Single Field Sort

Multiple Fields

Sorting on Related Fields

4. Relationships (Expand)

Basic Expand

Multiple Relationships

Nested Expand (Deep Loading)

Filtered Expand

5. Aggregations

Count

Group By with Aggregations

Aggregation Functions

Group By Multiple Fields

HAVING Clause

Date Bucketing

6. Advanced Queries

Distinct

Full-Text Search

Joins — removed (#4286)

Window Functions — removed from the request surface (#4286)

7. Pagination

Offset-Based Pagination

Keyset Pagination

8. Real-World Examples

CRM: Open Opportunities

E-Commerce: Product Search

Analytics: Revenue by Month

9. Error Handling

Unknown Fields Are Tolerated

Security Violations

Legacy Compatibility

Next Steps

Logical OR (`$or`)

Logical AND (`$and`)

Logical NOT (`$not`)