Objectstack Query

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

---

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](./rules/filters.md) — All operators, logical combinations, nested relations
• [Aggregation](./rules/aggregation.md) — GroupBy, aggregation functions, HAVING, window functions
• [Pagination](./rules/pagination.md) — 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

{ 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

{ 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

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

Null & Existence Operators

{ 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

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

---

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

---

Common Patterns

Expand vs Join: Which to Use?

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:

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.

objectstack-query — Evals

Test cases for the objectstack-query skill.

Planned Evals

1. Simple filter — "Filter accounts where status is active" 2. Nested relation filter — "Find orders where the customer's country is US" 3. Aggregation — "Count deals by region and show total revenue" 4. Pagination — "Implement cursor-based pagination for a list API" 5. Full-text search — "Search articles by keyword with fuzzy matching"

objectstack-query — Schema References

Auto-generated by packages/spec/scripts/build-skill-references.ts.

Do not edit — re-run pnpm --filter @objectstack/spec run gen:skill-refs to update.

Schemas live in the published @objectstack/spec package. Read them directly from node_modules — there is no local copy in the skill bundle.

Core schemas

• node_modules/@objectstack/spec/src/data/filter.zod.ts — Unified Query DSL Specification
• node_modules/@objectstack/spec/src/data/query.zod.ts — Sort Node

Transitive dependencies

• node_modules/@objectstack/spec/src/shared/lazy-schema.ts — Wrap a Zod schema constructor so its body is only evaluated on first use.

How to read these

1. The schemas are runtime Zod definitions. Use Read on the absolute path under node_modules/@objectstack/spec/src/ to inspect field shapes, .describe() text, enums, and refinements. 2. TypeScript types: import type { … } from '@objectstack/spec' (or the matching subpath export). 3. Runtime values: import { … } from '@objectstack/spec' — the package re-exports every schema and helper.

Aggregation Rules

Guide for building ObjectStack aggregation queries.

Aggregation Functions

Basic Aggregation

// SQL: SELECT COUNT(*) AS total_orders FROM order
{
  object: 'order',
  aggregations: [
    { function: 'count', alias: 'total_orders' }
  ]
}

Aggregation with GROUP BY

// SQL: SELECT region, SUM(amount) AS total, AVG(amount) AS average
//      FROM sale GROUP BY region
{
  object: 'sale',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' },
    { function: 'avg', field: 'amount', alias: 'average' }
  ],
  groupBy: ['region']
}

⚠️ CRITICAL: When using groupBy, you MUST include the grouped fields in fields array.

// ❌ Wrong: groupBy field not in fields
{
  object: 'sale',
  aggregations: [{ function: 'sum', field: 'amount', alias: 'total' }],
  groupBy: ['region']  // region not in fields!
}

// ✅ Correct: groupBy field included in fields
{
  object: 'sale',
  fields: ['region'],
  aggregations: [{ function: 'sum', field: 'amount', alias: 'total' }],
  groupBy: ['region']
}

HAVING Clause

Filter aggregated results (post-aggregation filtering):

// SQL: SELECT customer_id, COUNT(*) AS order_count
//      FROM order GROUP BY customer_id HAVING COUNT(*) > 5
{
  object: 'order',
  fields: ['customer_id'],
  aggregations: [
    { function: 'count', alias: 'order_count' }
  ],
  groupBy: ['customer_id'],
  having: {
    order_count: { $gt: 5 }
  }
}

Key difference: where filters rows BEFORE aggregation; having filters groups AFTER aggregation.

Filtered Aggregation (FILTER WHERE)

Apply a condition to a single aggregation without affecting others:

// SQL: SELECT
//   COUNT(*) AS total,
//   COUNT(*) FILTER (WHERE status = 'active') AS active_count
// FROM user
{
  object: 'user',
  aggregations: [
    { function: 'count', alias: 'total' },
    {
      function: 'count',
      alias: 'active_count',
      filter: { status: 'active' }
    }
  ]
}

DISTINCT Aggregation

// SQL: SELECT COUNT(DISTINCT department) FROM employee
{
  object: 'employee',
  aggregations: [
    { function: 'count_distinct', field: 'department', alias: 'dept_count' }
  ]
}

// Alternative: use distinct flag
{
  object: 'employee',
  aggregations: [
    { function: 'count', field: 'department', alias: 'dept_count', distinct: true }
  ]
}

Window Functions

Window functions compute values across row sets WITHOUT collapsing results.

ROW_NUMBER

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

Running Total

// Cumulative sum of transactions
{
  object: 'transaction',
  fields: ['date', 'amount'],
  windowFunctions: [
    {
      function: 'sum',
      field: 'amount',
      alias: 'running_total',
      over: {
        orderBy: [{ field: 'date', order: 'asc' }],
        frame: {
          type: 'rows',
          start: 'UNBOUNDED PRECEDING',
          end: 'CURRENT ROW'
        }
      }
    }
  ]
}

LAG / LEAD (Period-over-Period)

For dashboard widgets, prefer the higher-level `compareTo:

'previousPeriod' | 'previousYear' | { offset }` field on the widget

schema (see objectstack-ui → Period-over-period — `compareTo`).

The renderer issues the shifted query for you and aligns the result

bucket-for-bucket with categoryGranularity. Reach for the raw

lag / lead window functions below when you need the comparison

in a custom query result (reports, ad-hoc SQL, cube measures).

// Month-over-month comparison
{
  object: 'monthly_revenue',
  fields: ['month', 'revenue'],
  windowFunctions: [
    {
      function: 'lag',
      field: 'revenue',
      alias: 'prev_month_revenue',
      over: {
        orderBy: [{ field: 'month', order: 'asc' }]
      }
    }
  ]
}

Common Mistakes

❌ Wrong: Aggregation without alias

// ❌ alias is required
{
  aggregations: [
    { function: 'count' }
  ]
}

// ✅ Always provide alias
{
  aggregations: [
    { function: 'count', alias: 'total' }
  ]
}

❌ Wrong: Using where to filter aggregated results

// ❌ where filters BEFORE aggregation
{
  object: 'order',
  where: { order_count: { $gt: 5 } },  // order_count doesn't exist yet!
  aggregations: [{ function: 'count', alias: 'order_count' }],
  groupBy: ['customer_id']
}

// ✅ Use having to filter AFTER aggregation
{
  object: 'order',
  fields: ['customer_id'],
  aggregations: [{ function: 'count', alias: 'order_count' }],
  groupBy: ['customer_id'],
  having: { order_count: { $gt: 5 } }
}

❌ Wrong: sum/avg on non-numeric fields

// ❌ Cannot sum a string field
{
  aggregations: [
    { function: 'sum', field: 'name', alias: 'total' }
  ]
}

// ✅ sum/avg only work on numeric fields
{
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' }
  ]
}

Filter Rules

Comprehensive guide for building ObjectStack query filters.

Operator Reference

Implicit Equality (Shorthand)

The most common filter — equality — has a shorthand:

// ✅ Implicit equality (preferred for simple cases)
where: { status: 'active' }

// ✅ Explicit equality (same result)
where: { status: { $eq: 'active' } }

Logical Operators

AND (implicit)

All top-level conditions are AND-combined by default:

// ✅ Implicit AND — all conditions must match
where: {
  status: 'active',
  role: 'admin',
  age: { $gte: 18 }
}

// ✅ Explicit $and — same result
where: {
  $and: [
    { status: 'active' },
    { role: 'admin' },
    { age: { $gte: 18 } }
  ]
}

OR

// ✅ Find admins OR managers
where: {
  $or: [
    { role: 'admin' },
    { role: 'manager' }
  ]
}

// ✅ Equivalent using $in
where: {
  role: { $in: ['admin', 'manager'] }
}

NOT

// ✅ Exclude deleted records
where: {
  $not: { status: 'deleted' }
}

Combining Logical Operators

// ✅ Active users who are admin OR have high score
where: {
  status: 'active',            // AND
  $or: [
    { role: 'admin' },
    { score: { $gte: 90 } }
  ]
}

Field References

Compare a field against another field (not a literal value):

// ✅ Find records where actual exceeds budget
where: {
  actual_cost: { $gt: { $field: 'budget' } }
}

// ✅ Find overdue tasks (due_date before today is handled by runtime)
where: {
  end_date: { $lt: { $field: 'start_date' } }
}

Nested Relation Filters

Filter by a related object's fields:

// ✅ Find orders where the customer is in the US
where: {
  customer: {
    country: 'US'
  }
}

// ✅ Deeper nesting
where: {
  customer: {
    organization: {
      industry: 'Technology'
    }
  }
}

Common Mistakes

❌ Wrong: Multiple operators on different fields inside $or

// ❌ This is an AND, not an OR
where: {
  role: 'admin',
  status: 'active'
}
// Correct only if you want both conditions

// ✅ For OR, wrap in $or array
where: {
  $or: [
    { role: 'admin' },
    { status: 'active' }
  ]
}

❌ Wrong: Using string operators on non-string fields

// ❌ $contains only works on string fields
where: {
  age: { $contains: '25' }  // age is a number
}

// ✅ Use comparison operators for numbers
where: {
  age: { $eq: 25 }
}

❌ Wrong: Using $between with wrong tuple length

// ❌ $between requires exactly [min, max]
where: {
  price: { $between: [10, 50, 100] }
}

// ✅ Correct: exactly two elements
where: {
  price: { $between: [10, 50] }
}

❌ Wrong: Null check with equality

// ❌ Don't use equality to check for null
where: {
  deleted_at: null
}

// ✅ Use $null operator
where: {
  deleted_at: { $null: true }
}

Date Filtering Patterns

// Records created in the last 7 days (compute date in application code)
where: {
  created_at: { $gte: new Date('2025-01-01') }
}

// Records within a date range
where: {
  created_at: {
    $between: [new Date('2025-01-01'), new Date('2025-03-31')]
  }
}

Pagination Rules

Guide for implementing pagination in ObjectStack queries.

Strategies Overview

Offset Pagination

// Page 1 (first 20 records)
{
  object: 'post',
  where: { published: true },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
  offset: 0
}

// Page 3 (records 41–60)
{
  object: 'post',
  where: { published: true },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
  offset: 40
}

OData Compatibility

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

// These are equivalent
{ limit: 20 }
{ top: 20 }

Cursor Pagination

Cursor pagination uses the last record's sort key values to fetch the next page.

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

// Next page — pass the last record's values as cursor
{
  object: 'post',
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
  cursor: {
    created_at: '2025-01-15T10:30:00Z',
    id: 'post_abc123'
  }
}

⚠️ CRITICAL: Cursor keys MUST match the orderBy fields for correct pagination.

// ❌ Wrong: cursor fields don't match orderBy
{
  orderBy: [{ field: 'created_at', order: 'desc' }],
  cursor: { name: 'John' }  // name is not in orderBy!
}

// ✅ Correct: cursor fields match orderBy
{
  orderBy: [{ field: 'created_at', order: 'desc' }],
  cursor: { created_at: '2025-01-15T10:30:00Z' }
}

Sorting with Pagination

⚠️ CRITICAL: Always combine orderBy with pagination for stable results.

// ❌ Wrong: no orderBy — results are non-deterministic
{
  object: 'user',
  limit: 20,
  offset: 0
}

// ✅ Correct: explicit ordering guarantees stable pages
{
  object: 'user',
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 20,
  offset: 0
}

Multi-field Sorting

// Sort by status (asc), then by created date (newest first)
{
  object: 'task',
  orderBy: [
    { field: 'status', order: 'asc' },
    { field: 'created_at', order: 'desc' }
  ],
  limit: 50
}

REST API Pagination Pattern

When building paginated REST endpoints:

// GET /api/v1/posts?limit=20&offset=40
// Maps to:
{
  object: 'post',
  limit: 20,
  offset: 40,
  orderBy: [{ field: 'created_at', order: 'desc' }]
}

// Response includes pagination metadata
{
  data: [...],
  pagination: {
    total: 150,
    limit: 20,
    offset: 40,
    hasMore: true
  }
}

Common Mistakes

❌ Wrong: Mixing cursor and offset

// ❌ Don't use both cursor and offset
{
  object: 'post',
  limit: 20,
  offset: 40,
  cursor: { created_at: '2025-01-15T10:30:00Z' }
}

// ✅ Use one or the other
{
  object: 'post',
  limit: 20,
  cursor: { created_at: '2025-01-15T10:30:00Z' }
}

❌ Wrong: Large offset values

// ❌ Performance degrades with large offsets (DB must scan & discard rows)
{
  object: 'post',
  limit: 20,
  offset: 100000  // Very slow on large tables
}

// ✅ Use cursor pagination for deep pagination
{
  object: 'post',
  limit: 20,
  cursor: { created_at: '2024-06-01T00:00:00Z', id: 'post_xyz' }
}

❌ Wrong: Forgetting limit (unbounded queries)

// ❌ No limit — returns ALL records
{
  object: 'user',
  where: { status: 'active' }
}

// ✅ Always set a limit for list queries
{
  object: 'user',
  where: { status: 'active' },
  limit: 100,
  orderBy: [{ field: 'name', order: 'asc' }]
}

DISTINCT Queries

Remove duplicate rows from results:

{
  object: 'order',
  fields: ['customer_id', 'product_category'],
  distinct: true,
  orderBy: [{ field: 'customer_id', order: 'asc' }]
}