name

objectstack-query

description

Construct ObjectQL queries — filters, sorting, pagination, aggregation, relation expansion, and full-text search. Use when the user is writing a query DSL expression, picking pagination strategy, or designing a list view's filter spec. Do not use for defining objects / fields / relationships (see objectstack-data) or for designing the API endpoint that exposes a query (see objectstack-api).

license

Apache-2.0

compatibility

Requires @objectstack/spec 16.x (Zod v4 schemas)

metadata

author	version	domain	tags
objectstack-ai	1.2	query	query, filter, sort, paginate, aggregate, ObjectQL, full-text

Query Design — ObjectStack Query DSL

Expert instructions for constructing data queries using the ObjectStack Query DSL. This skill covers filter expressions, sorting, pagination, aggregation, full-text search, and the expand system for related records.

Schema vs. runtime: the QueryAST schema declares more than the engine currently executes. Sections below marked

⚠️ Schema-reserved — NOT executed by the engine yet.

describe properties that validate against the schema but are silently ignored (or rejected) at runtime. Never emit them in production queries — each caveat shows the working alternative.

Skill Boundaries

Need	Use instead
Define objects, fields, or relationships	objectstack-data
Define REST API endpoints or auth	objectstack-api
Build views, dashboards, or apps	objectstack-ui
Create a plugin or register services	objectstack-platform

When to Use This Skill

You are constructing a filter expression for record retrieval
You need to sort or paginate query results
You are writing aggregation queries (count, sum, avg, group by)
You need to expand related records through lookups
You are implementing full-text search across fields
You are choosing between offset vs keyset pagination

Core Concepts

Query Structure (QueryAST)

Every ObjectStack query follows the QuerySchema structure:

{
  object: 'account',           // Target object (required)
  fields: ['name', 'email'],   // SELECT — fields to retrieve
  where: { status: 'active' }, // WHERE — filter conditions
  orderBy: [{ field: 'created_at', order: 'desc' }],  // ORDER BY
  limit: 20,                   // LIMIT — max records
  offset: 0,                   // OFFSET — skip records
}

Key rule: object is the only required property. Everything else is optional.

Quick Reference — Detailed Rules

For comprehensive documentation with incorrect/correct examples:

Filters — All operators, logical combinations, nested relations, date macros
Aggregation — GroupBy, date bucketing, aggregation functions, driver support
Pagination — Offset vs keyset, best practices, performance

Filter Operators

ObjectStack uses a declarative, database-agnostic filter DSL inspired by Prisma, Strapi, and MongoDB.

Implicit Equality (Shorthand)

The simplest filter — field equals value:

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

Comparison Operators

Operator	Purpose	SQL Equivalent	Types
`$eq`	Equal	`=`	Any
`$ne`	Not equal	`<>`	Any
`$gt`	Greater than	`>`	Number, Date
`$gte`	Greater than or equal	`>=`	Number, Date
`$lt`	Less than	`<`	Number, Date
`$lte`	Less than or equal	`<=`	Number, Date

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

{ where: { created_at: { $gt: '2025-01-01' } } }
// SQL: WHERE created_at > '2025-01-01'

Set & Range Operators

Operator	Purpose	SQL Equivalent
`$in`	In list	`IN (...)`
`$nin`	Not in list	`NOT IN (...)`
`$between`	Inclusive range	`BETWEEN ? AND ?`

{ where: { status: { $in: ['active', 'pending'] } } }
// SQL: WHERE status IN ('active', 'pending')

{ where: { amount: { $between: [100, 500] } } }
// SQL: WHERE amount BETWEEN 100 AND 500

String Operators

Operator	Purpose	SQL Equivalent
`$contains`	Contains substring	`LIKE '%?%'`
`$notContains`	Does not contain	`NOT LIKE '%?%'`
`$startsWith`	Starts with prefix	`LIKE '?%'`
`$endsWith`	Ends with suffix	`LIKE '%?'`

{ where: { email: { $contains: '@company.com' } } }
// SQL: WHERE email LIKE '%@company.com%'

Null & Existence Operators

Operator	Purpose	SQL / NoSQL
`$null`	Is null check	`IS NULL` / `IS NOT NULL`
`$exists`	Field exists (NoSQL)	MongoDB `$exists`

{ where: { deleted_at: { $null: true } } }
// SQL: WHERE deleted_at IS NULL

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

Nested Relation Filters

Filter through relationships without an explicit join:

// Filter accounts where the related contact has a verified profile
{
  object: 'account',
  where: {
    contact: {                    // Relation field name
      profile: {                  // Nested relation
        verified: true
      }
    }
  }
}

Field References (Cross-Field Comparisons)

⚠️ Schema-reserved — NOT executed by the engine yet. $field exists only in the filter schema. No engine or driver code interprets it — the { $field: '...' } object binds as a literal value, so the query silently returns zero rows. Do not use it.

// ❌ Schema-valid but NOT executed — matches nothing
{
  where: {
    actual_revenue: { $gt: { $field: 'estimated_revenue' } }
  }
}

Working alternatives:

Define a formula field on the object that computes the comparison (e.g. exceeds_estimate as a boolean), then filter on it: { where: { exceeds_estimate: true } } (see objectstack-data).
Fetch both fields and compare in application code.

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 Pagination (Simple)

{
  object: 'account',
  limit: 20,
  offset: 40,   // Skip first 40 records (page 3)
}

When to use: UI pages, small datasets (<100K records), when you need "jump to page N".

Pitfall: Offset pagination degrades on large offsets — the database still scans skipped rows.

Keyset Pagination (Performant)

⚠️ cursor is schema-reserved — NOT executed by the engine yet. The cursor property validates against QuerySchema, but no engine or driver code reads it — a query carrying cursor silently returns page 1 forever. Do keyset pagination manually with where + orderBy + limit:

// First page
{
  object: 'account',
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
}

// Next page — filter past the last record you've seen
{
  object: 'account',
  where: { created_at: { $lt: lastSeenCreatedAt } },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
}

When to use: Infinite scroll, APIs, large datasets, real-time feeds.

Rule: The keyset where field must match the orderBy field (use a unique or near-unique column such as created_at or id) so WHERE created_at < ? picks up exactly where the previous page ended.

OData Compatibility

top is an alias for limit (for OData-style APIs):

{ object: 'account', top: 50 }
// Equivalent to: { object: 'account', limit: 50 }

Aggregation

Basic Aggregation Functions

Function	Purpose	SQL
`count`	Count rows	`COUNT(*)` or `COUNT(field)`
`sum`	Sum values	`SUM(field)`
`avg`	Average	`AVG(field)`
`min`	Minimum	`MIN(field)`
`max`	Maximum	`MAX(field)`
`count_distinct`	Unique count	`COUNT(DISTINCT field)`
`array_agg`	Collect into array	`ARRAY_AGG(field)`
`string_agg`	Concatenate strings	`STRING_AGG(field, ',')`

⚠️ Driver support varies. On SQL datasources the driver executes only count / sum / avg / min / max and throws on count_distinct, array_agg, and string_agg; the per-aggregation distinct: true flag is also ignored there. The in-memory fallback path (driver-rest, driver-memory, timezone/date-bucket fallbacks) supports all 8 functions plus distinct. For portable queries, stick to the first five.

GroupBy + Aggregation

// Total revenue per region
{
  object: 'deal',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
    { function: 'count', alias: 'deal_count' },
  ],
  groupBy: ['region'],
  orderBy: [{ field: 'total_revenue', order: 'desc' }],
}
// SQL: SELECT region, SUM(amount) AS total_revenue, COUNT(*) AS deal_count
//      FROM deal GROUP BY region ORDER BY total_revenue DESC

groupBy entries can also be structured objects for date bucketing — { field: 'closed_at', dateGranularity: 'quarter' } — see Aggregation rules for the full pattern.

HAVING Clause

⚠️ Schema-reserved — NOT executed by the engine yet. having validates against QuerySchema, but EngineAggregateOptions has no having property and nothing implements it — the clause is silently dropped. Working alternative: post-filter the aggregated rows in application code:

// ❌ having is silently ignored — do NOT rely on it
// { ..., having: { total_revenue: { $gt: 100000 } } }

// ✅ Aggregate, then filter the result rows in app code
const rows = await engine.aggregate('deal', {
  groupBy: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
  ],
});
const bigRegions = rows.filter((r) => r.total_revenue > 100000);

Filtered Aggregation

⚠️ Per-aggregation filter is schema-reserved — NOT executed by the engine yet. The SQL driver ignores it and the in-memory path ignores it too, so a filter-carrying aggregation returns the unfiltered number — silently wrong results. Working alternative: issue one aggregate call per condition, moving the condition into the query-level where:

// ❌ filter on the aggregation is silently ignored
// { function: 'count', alias: 'high_value_orders',
//   filter: { amount: { $gt: 1000 } } }

// ✅ Separate aggregate calls, condition in `where`
const [totals] = await engine.aggregate('order', {
  aggregations: [{ function: 'count', alias: 'total_orders' }],
});
const [highValue] = await engine.aggregate('order', {
  where: { amount: { $gt: 1000 } },
  aggregations: [{ function: 'count', alias: 'high_value_orders' }],
});

Expand (Related Records)

Load related records through lookup/master_detail fields:

{
  object: 'task',
  fields: ['title', 'status'],
  expand: {
    assignee: {
      object: 'user',
      fields: ['name', 'email'],
    },
    project: {
      object: 'project',
      fields: ['name'],
      expand: {
        org: { object: 'org', fields: ['name'] }  // Nested expand
      }
    }
  }
}

Rules:

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.

Joins

⚠️ Schema-reserved — NOT executed by the engine yet. joins (and the JoinStrategy hints) exist only in the QueryAST schema — no engine or driver code consumes them, regardless of a driver advertising supports.joins. A query carrying joins behaves as if they were absent.

Working alternatives (both implemented):

expand — load related records through lookup / master_detail fields (see previous section).
Nested relation filters — filter a parent by conditions on a related object without an explicit join:

// Orders whose customer is in the US — no join needed
{
  object: 'order',
  fields: ['id', 'amount'],
  where: { customer: { country: 'US' } },
}

Full-Text Search

Only the query + fields subset of the search schema executes. The engine expands the search string into a driver-agnostic filter: each term becomes an $or of $contains predicates across the resolved searchable fields, and multiple whitespace-separated terms are AND-ed (every term must hit some field). Matching is case-insensitive; select/status fields match by option label, mapped to stored values.

{
  object: 'article',
  search: {
    query: 'machine learning',
    fields: ['title', 'content'],
  },
  limit: 10,
}
// Executes as:
// { $and: [
//   { $or: [{ title: { $contains: 'machine' } }, { content: { $contains: 'machine' } }] },
//   { $or: [{ title: { $contains: 'learning' } }, { content: { $contains: 'learning' } }] },
// ]}

Omit fields to search the object's declared searchableFields (or an auto-default of name/title + short-text fields), resolved server-side.

⚠️ Schema-reserved — NOT executed by the engine yet: fuzzy, boost, operator, minScore, language, and highlight validate against the schema but are never read. Terms are always AND-ed; there is no relevance scoring or highlighting.

Window Functions (Analytics)

⚠️ Schema-reserved — NOT executed by the engine yet. windowFunctions exists in the QueryAST schema, but the engine never routes it to any driver — the property is silently dropped from ordinary queries. (Even the SQL driver's internal builder drops the field argument, so lag(revenue) would render as LAG().) Do not emit windowFunctions.

Working alternatives:

Ranking / top-N per group and running totals: model them in report/dashboard metadata (groupings, measures, dateGranularity bucketing, compareTo for period-over-period) — see objectstack-ui.
Ad-hoc analysis: fetch the ordered rows (orderBy + limit) and compute ranks or running sums in application code.

Window Function Enum (schema-reserved)

For completeness, the full WindowFunction enum declared by the schema: row_number, rank, dense_rank, percent_rank, lag, lead, first_value, last_value, sum, avg, count, min, max. None of these execute today.

Common Patterns

Cross-Object Queries: Which Tool to Use?

Scenario	Use
Load lookup fields for display	`expand`
Filter parent by child conditions	Nested relation filter
Simple parent→child navigation	`expand`
Paginate/sort a parent's related records	Query the related object directly
Analytical queries across objects	Report/dashboard metadata, or separate queries combined in app code (`joins` is schema-reserved — see above)

Pagination Pattern for APIs

// Page-based API response
{
  object: 'account',
  where: { status: 'active' },
  fields: ['id', 'name', 'email'],
  orderBy: [{ field: 'name', order: 'asc' }],
  limit: 20,
  offset: (page - 1) * 20,
}

Dashboard Aggregation Pattern

Unconditional KPIs can share one aggregate call; a KPI with its own condition needs a separate call with the condition in where (per-aggregation filter is schema-reserved — see Filtered Aggregation):

// KPI dashboard: unconditional aggregations share one call
const [kpis] = await engine.aggregate('deal', {
  aggregations: [
    { function: 'count', alias: 'total_deals' },
    { function: 'sum', field: 'amount', alias: 'pipeline_value' },
    { function: 'avg', field: 'amount', alias: 'avg_deal_size' },
  ],
});

// Conditional KPI: separate call, condition in `where`
const [won] = await engine.aggregate('deal', {
  where: { stage: 'closed_won' },
  aggregations: [{ function: 'count', alias: 'won_deals' }],
});

CRM Analytics Query Blueprint

Model analytics in dashboard/report metadata rather than hand-written query code — the renderer issues the queries for you:

Query Need	Pattern
KPI widgets	Aggregates (`sum`, `count`, `avg`) over the object, each conditional KPI scoped by the widget/dataset filter. Add `compareTo: 'previousPeriod' \| 'previousYear'` on the widget for a one-line period-over-period delta.
Time-series chart	Date filters + `categoryGranularity: 'day' \| 'week' \| 'month' \| 'quarter' \| 'year'` for server-side bucketing — never bucket by hand on the client. Pair with `compareTo` for an aligned YoY overlay.
Matrix report	`groupingsDown` + `groupingsAcross` + `dateGranularity: 'quarter'`
Funnel summary	Multi-level grouping (`owner -> stage`) + aggregated measures
Operational filter	Prefer declarative operators (`$ne`, `$nin`, `$gte`) over hardcoded SQL

For metadata app development, model analytics in report/dashboard metadata first; only fall back to custom query code when schema limits require it.

Verify your work

Most queries run at runtime (smoke-test them with os data query or a vitest test), but query metadata — list-view filter specs and report/dashboard datasets — is validated statically. After editing those, run:

os validate     # schema + CEL predicates + widget/dataset bindings (no artifact)
# or: os build  # the same gates, plus emits dist/

A dashboard widget whose dataset / dimensions / values don't resolve fails here instead of rendering an empty chart (ADR-0021). In a scaffolded project the gate is npm run validate. See objectstack-platform → Verify your work.

References

See references/_index.md for the full list of Zod schemas (with one-line descriptions) — pointers into node_modules/@objectstack/spec/src/. Always Read the source for exact field shapes; do not rely on memory of property names.

Uh oh!

FilesExpand file tree

SKILL.md

Latest commit

History