objectstack-query

name	objectstack-query
description	Construct ObjectQL queries — filters, sorting, pagination, aggregation, joins/expansion, window functions, 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 Zod schemas (v4+)
metadata	{"author":"objectstack-ai","version":"1.1","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, joins, window functions, full-text search, and the expand system for related records.

---

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 need window functions for analytical queries
• You are choosing between offset vs cursor 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
• Aggregation — GroupBy, aggregation functions, HAVING, window functions
• Pagination — Offset vs cursor, 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)

Compare two fields using $field:

// Where actual_revenue > estimated_revenue
{
  where: {
    actual_revenue: { $gt: { $field: 'estimated_revenue' } }
  }
}

---

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.

Cursor Pagination (Performant)

{
  object: 'account',
  limit: 20,
  cursor: { id: 'last-seen-id' },
  orderBy: [{ field: 'id', order: 'asc' }],
}

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

Rule: The cursor fields must match orderBy fields. The engine uses them to generate WHERE id > ? instead of OFFSET.

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, ',')

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

HAVING Clause

Filter groups after aggregation:

{
  object: 'deal',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total_revenue' },
  ],
  groupBy: ['region'],
  having: { total_revenue: { $gt: 100000 } },
}
// SQL: ... HAVING SUM(amount) > 100000

Filtered Aggregation

Apply a filter to a specific aggregation only:

{
  object: 'order',
  aggregations: [
    { function: 'count', alias: 'total_orders' },
    { function: 'count', alias: 'high_value_orders',
      filter: { amount: { $gt: 1000 } } },
  ],
}
// SQL: COUNT(*) AS total_orders,
//      COUNT(*) FILTER (WHERE amount > 1000) AS 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 full QueryAST — you can filter, sort, and paginate within it

---

Joins (Advanced)

For cross-object queries beyond what expand provides:

{
  object: 'order',
  fields: ['id', 'amount'],
  joins: [
    {
      type: 'inner',       // 'inner' | 'left' | 'right' | 'full'
      object: 'customer',
      alias: 'c',
      on: { 'order.customer_id': { $eq: { $field: 'c.id' } } },
    }
  ],
}

Join Strategy Hints

Strategy	When to use
auto	Default — engine decides
database	Both objects on same datasource
hash	Cross-datasource, moderate data
loop	Small right-side lookup table

---

Full-Text Search

{
  object: 'article',
  search: {
    query: 'machine learning',
    fields: ['title', 'content'],
    fuzzy: true,
    boost: { title: 2.0 },
    highlight: true,
  },
  limit: 10,
}

Options:

• fuzzy: true — tolerates typos
• boost — field-specific relevance weighting
• operator: 'and' | 'or' — match all terms or any term
• minScore — minimum relevance threshold
• language — text analysis language

---

Window Functions (Analytics)

Window functions compute values across row sets without collapsing results:

// Rank products by sales within each category
{
  object: 'product',
  fields: ['name', 'category', 'sales'],
  windowFunctions: [
    {
      function: 'row_number',
      alias: 'category_rank',
      over: {
        partitionBy: ['category'],
        orderBy: [{ field: 'sales', order: 'desc' }],
      }
    }
  ],
}

Available Window Functions

Function	Purpose
row_number	Sequential number within partition
rank	Rank with gaps for ties
dense_rank	Rank without gaps
lag / lead	Access previous/next row value
first_value / last_value	First/last value in window
sum / avg / count / min / max	Running aggregates

---

Common Patterns

Expand vs Join: Which to Use?

Scenario	Use
Load lookup fields for display	expand
Filter parent by child conditions	Nested relation filter
Cross-datasource joins	joins with strategy: 'hash'
Analytical queries across tables	joins
Simple parent→child navigation	expand

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

// KPI dashboard: multiple aggregations on same object
{
  object: 'deal',
  aggregations: [
    { function: 'count', alias: 'total_deals' },
    { function: 'sum', field: 'amount', alias: 'pipeline_value' },
    { function: 'avg', field: 'amount', alias: 'avg_deal_size' },
    { function: 'count', alias: 'won_deals',
      filter: { stage: 'closed_won' } },
  ],
}

---

CRM Analytics Query Blueprint

Use dashboards/reports metadata as the practical query pattern source:

Query Need	CRM Reference	Pattern
KPI widgets	dashboards/sales.dashboard.ts	Filtered aggregates (sum, count, avg) over opportunity. Add compareTo: 'previousPeriod' | 'previousYear' on the widget for a one-line period-over-period delta.
Time-series chart	dashboards/sales.dashboard.ts	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	reports/opportunity.report.ts	groupingsDown + groupingsAcross + dateGranularity: 'quarter'
Funnel summary	reports/opportunity.report.ts	Multi-level grouping (owner -> stage) + aggregated measures
Operational filter	dashboard/report filters	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.