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

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' } }`
`$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 } }`

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

Pagination

Offset-Based Pagination

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

Cursor-Based Pagination (Keyset)

{
  limit: 20,
  cursor: { id: 100 }  // Opaque keyset cursor (e.g. last seen sort-key values)
}

cursor is an opaque record (Record<string, unknown>) carrying the keyset position from the previous page. There is no keyset/after query property.

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. Use expand to pull in a relationship's fields instead.

Load related records through lookup / master_detail fields with expand. Each key is a relationship field name; the value is a nested query that can select fields, filter, and expand further (default max depth: 3).

{
  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. Per-parent limit / offset / orderBy are not applied on this path.

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' }`
`array_agg`	Collect into array	`{ function: 'array_agg', field: 'tag', alias: 'all_tags' }`
`string_agg`	Concatenate strings	`{ function: 'string_agg', field: 'name', alias: 'names' }`

count_distinct / array_agg / string_agg are only fully supported on the MongoDB driver. The SQL driver's aggregate function mapper throws Unsupported aggregate function for all three (only count/sum/avg/min/max are mapped), and the in-memory driver's aggregator silently returns null for them. Avoid these three on SQL- or memory-backed objects.

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 accepted by QuerySchema but is not enforced by the query engine today. EngineAggregateOptions (the type ObjectQL.aggregate() actually takes) has no having field, and both the REST findData() dispatcher and ObjectQL.aggregate() build their driver-facing query from only where / groupBy / aggregations — a having clause is silently dropped before it reaches any driver. No driver (SQL, in-memory, MongoDB) filters on it either. Post-filter grouped results on the client until this is wired up.

Joins

joins is defined in QuerySchema (JoinNodeSchema) and the SQL driver advertises supports.joins: true, but no driver's find() actually executes a join today — the SQL, in-memory, and MongoDB drivers all ignore the joins array (there is no .join()/.leftJoin() call in the SQL driver's query builder). Use expand for relationship traversal instead.

Join Types

Type	Description
`inner`	Records with matches in both objects
`left`	All records from left + matching from right
`right`	All records from right + matching from left
`full`	All records from both objects

Join Strategies

Strategy	Description
`auto`	Let the engine choose the best strategy
`database`	Push join to the database level
`hash`	In-memory hash join
`loop`	Nested loop join

Join Example

{
  object: 'order',
  fields: ['id', 'total', 'customer.name', 'customer.email'],
  joins: [
    {
      type: 'inner',
      object: 'customer',
      on: ['order.customer_id', '=', 'customer.id'],
      strategy: 'auto'
    }
  ]
}

Full-Text Search

{
  object: 'article',
  search: {
    query: 'kubernetes deployment',
    fields: ['title', 'body', 'tags'],
    fuzzy: true,
    operator: 'and',
    boost: { title: 2.0, body: 1.0 },
    minScore: 0.5,
    language: 'english',
    highlight: true
  }
}

Property	Type	Description
`query`	`string`	Search text
`fields`	`string[]`	Fields to search (optional — defaults to all searchable)
`fuzzy`	`boolean`	Enable fuzzy matching for typo tolerance
`operator`	`'and' \| 'or'`	How to combine search terms
`boost`	`Record<string, number>`	Field relevance weights
`minScore`	`number`	Minimum relevance score (0–1)
`language`	`string`	Language for stemming/stopwords
`highlight`	`boolean`	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. Multiple search terms are always AND-ed regardless of operator.

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

windowFunctions is only reachable by calling the SQL driver's findWithWindowFunctions() method directly. ObjectQL.find() / .aggregate() and the POST /api/v1/data/:object/query route never call it, so sending windowFunctions through the standard client/REST query path has no effect.

Function	Description
`row_number`	Sequential row number within partition
`rank`	Rank with gaps for ties
`dense_rank`	Rank without gaps for ties
`percent_rank`	Relative rank as percentage
`lag`	Access previous row value
`lead`	Access next row value
`first_value`	First value in window
`last_value`	Last value in window
`sum` / `avg` / `count` / `min` / `max`	Running aggregations

Window Function Example

{
  object: 'employee',
  fields: ['name', 'department', 'salary'],
  windowFunctions: [
    {
      function: 'rank',
      alias: 'salary_rank',
      over: {
        partitionBy: ['department'],
        orderBy: [{ field: 'salary', order: 'desc' }]
      }
    }
  ]
}

Distinct & Group By

Distinct Records

{
  object: 'task',
  fields: ['category'],
  distinct: true
}

The top-level distinct: true flag is defined in QuerySchema but isn't applied by any driver's find() (SQL, in-memory, and MongoDB all ignore it). A separate driver.distinct(object, field) method exists on the SQL and in-memory drivers, but it isn't called by ObjectQL.find()/.aggregate(), so it isn't reachable through a normal query.

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 is not currently enforced — it's dropped before reaching the aggregation engine or any driver.

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.name', 'created_at'],
  where: {
    created_at: { $gte: '2026-01-01T00:00:00Z' }
  },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 10
}

Query Syntax Cheat Sheet

On this page