ObjectStackObjectStack

Query Syntax Cheat Sheet

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

OperatorDescriptionExample
$eqEqual to{ status: { $eq: 'open' } }
$neNot equal to{ status: { $ne: 'closed' } }
$gtGreater than{ amount: { $gt: 100 } }
$gteGreater than or equal{ amount: { $gte: 100 } }
$ltLess than{ amount: { $lt: 1000 } }
$lteLess than or equal{ amount: { $lte: 1000 } }

Set Operators

OperatorDescriptionExample
$inValue is in array{ status: { $in: ['open', 'pending'] } }
$ninValue is NOT in array{ status: { $nin: ['closed', 'cancelled'] } }

Range Operator

OperatorDescriptionExample
$betweenValue is in range (inclusive){ price: { $between: [10, 100] } }

String Operators

OperatorDescriptionExample
$containsString contains substring{ title: { $contains: 'urgent' } }
$startsWithString starts with{ email: { $startsWith: 'admin' } }
$endsWithString ends with{ email: { $endsWith: '@acme.com' } }

Null / Existence Operators

OperatorDescriptionExample
$nullValue is null{ deleted_at: { $null: true } }
$existsField 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' }
  ]
}
PropertyTypeDescription
fieldstringField 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,
  keyset: {
    after: 'eyJpZCI6MTAwfQ==',  // Cursor from previous response
    field: 'id',
    order: 'asc'
  }
}

Field Selection

Select Specific Fields

{
  fields: ['id', 'title', 'status', 'created_at']
}

Nested / Related Fields

{
  fields: [
    'id',
    'title',
    { field: 'assigned_to', alias: 'assignee' }
  ]
}

Aggregations

Available Functions

FunctionDescriptionExample
countCount records{ function: 'count', alias: 'total' }
sumSum numeric field{ function: 'sum', field: 'amount', alias: 'total_amount' }
avgAverage numeric field{ function: 'avg', field: 'rating', alias: 'avg_rating' }
minMinimum value{ function: 'min', field: 'price', alias: 'min_price' }
maxMaximum value{ function: 'max', field: 'price', alias: 'max_price' }
count_distinctCount unique values{ function: 'count_distinct', field: 'category', alias: 'categories' }
array_aggCollect into array{ function: 'array_agg', field: 'tag', alias: 'all_tags' }
string_aggConcatenate strings{ function: 'string_agg', field: 'name', alias: 'names' }

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

Joins

Join Types

TypeDescription
innerRecords with matches in both objects
leftAll records from left + matching from right
rightAll records from right + matching from left
fullAll records from both objects

Join Strategies

StrategyDescription
autoLet the engine choose the best strategy
databasePush join to the database level
hashIn-memory hash join
loopNested 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',
  fullTextSearch: {
    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
  }
}
PropertyTypeDescription
querystringSearch text
fieldsstring[]Fields to search (optional — defaults to all searchable)
fuzzybooleanEnable fuzzy matching for typo tolerance
operator'and' | 'or'How to combine search terms
boostRecord<string, number>Field relevance weights
minScorenumberMinimum relevance score (0–1)
languagestringLanguage for stemming/stopwords
highlightbooleanReturn highlighted matches

Window Functions

FunctionDescription
row_numberSequential row number within partition
rankRank with gaps for ties
dense_rankRank without gaps for ties
percent_rankRelative rank as percentage
lagAccess previous row value
leadAccess next row value
first_valueFirst value in window
last_valueLast value in window
sum / avg / count / min / maxRunning aggregations

Window Function Example

{
  object: 'employee',
  fields: ['name', 'department', 'salary'],
  windowFunctions: [
    {
      function: 'rank',
      alias: 'salary_rank',
      partitionBy: ['department'],
      orderBy: [{ field: 'salary', order: 'desc' }]
    }
  ]
}

Distinct & Group By

Distinct Records

{
  object: 'task',
  fields: ['category'],
  distinct: true
}

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

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',
  fullTextSearch: { 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
}

On this page

Query Syntax Cheat SheetBasic Query StructureFilter OperatorsComparison OperatorsSet OperatorsRange OperatorString OperatorsNull / Existence OperatorsLogical Operators$and — All conditions must match$or — Any condition can match$not — Negate a conditionNested LogicSortingPaginationOffset-Based PaginationCursor-Based Pagination (Keyset)Field SelectionSelect Specific FieldsNested / Related FieldsAggregationsAvailable FunctionsAggregation ExampleJoinsJoin TypesJoin StrategiesJoin ExampleFull-Text SearchWindow FunctionsWindow Function ExampleDistinct & Group ByDistinct RecordsGroup By with HavingCommon Query PatternsList with PaginationSearch with FilterDashboard AggregationRecent Activity