objectstack-query

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":"objectstack-ai","version":"1.2","domain":"query","tags":"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 '[e-mail]%'

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.