objectstack-ai/framework/objectstack-query

Query Design — ObjectStack Query DSL

Calling Convention — object is the FIRST ARGUMENT

ENGINE_FIND_OPTION_KEYS / ENGINE_AGGREGATE_OPTION_KEYS are closed sets: a key outside the row is refused by name (find('task') does not recognise option 'bogus'), never ignored. A standalone { object: 'account', limit: 20 } literal is therefore a `QueryAST` — legal as findData's query or an expand value — not an engine option bag. top folds to limit, filter to where, before that check.

The passthrough six ride along on find/findOne (and on update/delete) because there the option bag IS the base of the driver options, which is how an explicit tenantId reaches the driver. count and aggregate never forward the bag, so on those two the same keys are deliberately ILLEGAL — accepting them would be the silently-ignored option this check exists to close. The one exception is timezone: aggregate reads it itself, for date bucketing, so it is legal there and the row above lists it; count refuses it with the rest.

Which filter dialect?

Execution Context (context)

The RLS / system-read escape hatch. A hook, job or endpoint that reads without one runs as whatever identity the caller carried — org-scoping hooks then return fewer rows, indistinguishable from "there is no data".

const SYS = { isSystem: true } as const;
const [row] = await engine.find('project', { where: { name }, limit: 1, context: SYS });

Pass any SUBSET of the execution envelope (identity, tenant, transaction): { isSystem: true } for a system read, otherwise the caller's own context (user, position or permission set), or 403 PERMISSION_DENIED (ADR-0096 D5). On the READ methods it may sit in the query bag (above) OR in the trailing options argument, engine.find(obj, query, { context }); the trailing one wins when both are given. Writes take only the trailing argument.

Removed Keys → Live Replacement

All six are tombstoned in @objectstack/spec 17: tsc types them never, and a query carrying one fails to parse with the upgrade prescription. The retirement procedure and the full tombstone register are objectstack-upgrade.

Quick Reference — Detailed Rules

• [Filters](./rules/filters.md) — all operators, logical combinations, filtering by a related record, now-anchored comparands, date macros and session tokens
• [Aggregation](./rules/aggregation.md) — groupBy, date bucketing, functions, having, per-measure filter
• [Pagination](./rules/pagination.md) — offset vs keyset, best practices, performance

Filter Operators

Implicit Equality (Shorthand)

The simplest filter — field equals value:

{ where: { status: 'active' } }
// SQL: WHERE status = 'active'

Comparison Operators

{ where: { age: { $gte: 18 } } }
// SQL: WHERE age >= 18

Set & Range Operators

{ where: { status: { $in: ['active', 'pending'] } } }
{ where: { amount: { $between: [100, 500] } } }

String Operators

$contains / $notContains / $startsWith / $endsWith compare CASE-SENSITIVELY; $icontains is the case-INSENSITIVE twin (ASCII folding only). So the user-facing cases want $icontains:

{ where: { email: { $icontains: '@company.com' } } }

Full table, $like portability and the $ilike boundary: [filter rules](./rules/filters.md).

Null & Existence Operators

{ where: { deleted_at: { $null: true } } }
{ where: { notes: { $empty: true } } }   // false = has content

A view filter's is_empty / is_not_empty lowers to $empty. A face that judges by declared type refuses it on a column it holds no declaration for (the built-in id, for one) — use $null there.

Logical Operators

Combine conditions with $and, $or, and $not:

// OR: active accounts OR accounts with high revenue
{ where: { $or: [{ status: 'active' }, { revenue: { $gt: 1000000 } }] } }
// AND + OR combined
{
  where: {
    $and: [
      { type: 'enterprise' },
      { $or: [{ region: 'us' }, { region: 'eu' }] },
    ]
  }
}
// NOT: exclude closed accounts
{ where: { $not: { status: 'closed' } } }

Filtering by a related record

{ customer: { country: 'US' } } beneath a lookup is served in where: the engine reads the related object as the caller and matches its ids. The limits (one level, forward only, where only, 1000 ids, the caller's permissions) and the two-step route past them: [filter rules → Relation Filters](./rules/filters.md).

Cross-field comparisons

{ $field: '...' } compares two columns of the same row, in a comparison position only; { $now: true, addDays: OFFSET } is the instant the query runs at, shifted by whole days a number field of the record holds — see [filter rules → Field References / Now-Anchored Comparands](./rules/filters.md).

Sorting

Sort with orderBy — an array of sort nodes:

{
  object: 'account',
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'name', order: 'asc' },      // Secondary sort
  ]
}

Rules:

• Order of array elements defines sort priority
• Default order is 'asc' — you can omit it for ascending sorts
• Sort fields should be indexed for performance (see objectstack-data indexing rules)

Pagination

// Offset paging — page 3
{ object: 'account', limit: 20, offset: 40 }

When to use: UI pages, small datasets, "jump to page N". It degrades on large offsets — the database still scans the skipped rows.

For keyset paging (infinite scroll, APIs, large datasets, real-time feeds), filter past the last row you saw with a where on the sort key, and always orderBy that same field in that same direction — the pattern, the direction rule and the pitfalls are [pagination rules](./rules/pagination.md).

Aggregation

The functions (count, sum, avg, min, max, count_distinct), date bucketing, having, and the per-measure filter are [aggregation rules](./rules/aggregation.md). The call shape:

// Total revenue per region
const rows = await engine.aggregate('deal', {
  groupBy: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
    { function: 'count', alias: 'deal_count' },
  ],
});
// SQL: SELECT region, SUM(amount) AS total_revenue, COUNT(*) AS deal_count
//      FROM deal GROUP BY region

fields and orderBy are NOT in ENGINE_AGGREGATE_OPTION_KEYS — do not put them in an aggregate bag. Grouped fields are auto-selected into the result rows; read each measure under its alias, and reference that same name from having. groupBy entries may be objects for date bucketing — { field: 'closed_at', dateGranularity: 'quarter' }.

Expand (Related Records)

Load related records through lookup / master_detail fields. Keep the foreign key in `fields` — the relation is carried by that column:

const tasks = await engine.find('task', {
  fields: ['title', 'status', 'assignee', 'project'],   // the FK columns stay
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      fields: ['name'],
      expand: { org: { object: 'org', fields: ['name'] } },   // nested expand
    },
  },
});

Rules:

• The projection must RETAIN the foreign-key column. fields: ['title']

with expand: { project: … } resolves nothing: the engine reads the FK off each record and skips the relation when it is absent, so the call returns rows with no related data and no error.

• Max expand depth is 3 by default
• The engine resolves expands via batch $in queries (not N+1)
• Keys in expand must be lookup or master_detail field names
• Each expand value is a nested QueryAST, but the engine applies **select

(fields) and filter (where) only** — per-parent limit / offset / orderBy are NOT applied on this path. To paginate or sort related records, query the related object directly.

Full-Text Search

The canonical form is a bare string with a sibling searchFields:

const rows = await engine.find('article', {
  search: 'machine learning',
  searchFields: ['title', 'content'],
  limit: 10,
});
// Executes as:
// { $and: [
//   { $or: [{ title: { $icontains: 'machine' } }, { content: { $icontains: 'machine' } }] },
//   { $or: [{ title: { $icontains: 'learning' } }, { content: { $icontains: 'learning' } }] },
// ]}