Skip to content

queries.mdx

Latest commit

 

History

History
521 lines (421 loc) · 13.5 KB

queries.mdx

File metadata and controls

521 lines (421 loc) · 13.5 KB
Raw
title Query Syntax Cheat Sheet
description 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`) 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']
}

Nested / Related Fields

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

Expand (Related Records)

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.

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](#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
}