Query Syntax

Database-agnostic query language with filtering, joins, aggregations, and sorting — aligned with the canonical @objectstack/spec QuerySchema

ObjectQL queries are expressed as Abstract Syntax Trees (AST) in JSON format. This enables database-agnostic querying—write once, compile to PostgreSQL, MongoDB, Redis, or any supported driver.

All query syntax in this document follows the canonical QuerySchema defined in @objectstack/spec (packages/spec/src/data/query.zod.ts). Filtering uses the where + MongoDB-style $op object syntax from FilterConditionSchema (packages/spec/src/data/filter.zod.ts).

Query Philosophy

Traditional SQL:

-- Tightly coupled to PostgreSQL
SELECT c.name, c.email, a.company_name
FROM contact c
LEFT JOIN account a ON c.account_id = a.id
WHERE c.is_active = true AND a.industry = 'tech'
ORDER BY c.created_at DESC
LIMIT 10;

ObjectQL (Canonical Spec Format):

import type { QueryAST } from '@objectstack/spec/data';

const query: QueryAST = {
  object: 'contact',
  fields: ['name', 'email', { field: 'account', fields: ['company_name'] }],
  where: {
    is_active: true,
    'account.industry': 'tech',
  },
  orderBy: [{ field: 'created_at', order: 'desc' }],
  limit: 10,
};

Runtime compilation:

PostgreSQL → Optimized SQL with JOINs
MongoDB → Aggregation pipeline with $lookup
Redis → Key pattern matching + Lua script
Excel → Filter + VLOOKUP formulas

Query Structure

The QueryAST (Canonical)

The canonical query structure is defined by QuerySchema in @objectstack/spec:

import type { QueryAST } from '@objectstack/spec/data';

// QueryAST — full structure
interface QueryAST {
  object: string;                          // Target object (required)
  fields?: FieldNode[];                    // Projection (SELECT)
  where?: FilterCondition;                 // Filtering (WHERE) — MongoDB-style $op
  search?: FullTextSearch;                 // Full-text search ($search)
  orderBy?: SortNode[];                    // Ordering (ORDER BY)
  limit?: number;                          // Max records (LIMIT)
  offset?: number;                         // Skip records (OFFSET)
  top?: number;                            // Alias for limit (OData compat)
  cursor?: Record<string, unknown>;        // Keyset pagination cursor
  joins?: JoinNode[];                      // Explicit JOINs
  aggregations?: AggregationNode[];        // Aggregation functions
  groupBy?: string[];                      // GROUP BY fields
  having?: FilterCondition;                // HAVING clause
  windowFunctions?: WindowFunctionNode[];  // Window functions (OVER)
  distinct?: boolean;                      // SELECT DISTINCT
  expand?: Record<string, QueryAST>;       // Recursive relation loading
}

Key Types

// SortNode — ORDER BY element
interface SortNode {
  field: string;
  order: 'asc' | 'desc';   // default: 'asc'
}

// AggregationNode — aggregation definition
interface AggregationNode {
  function: 'count' | 'sum' | 'avg' | 'min' | 'max'
          | 'count_distinct' | 'array_agg' | 'string_agg';
  field?: string;        // optional for COUNT(*)
  alias: string;         // result column alias
  distinct?: boolean;    // DISTINCT before aggregation
  filter?: FilterCondition; // FILTER WHERE clause
}

// FieldNode — field selection
type FieldNode = string | {
  field: string;
  fields?: FieldNode[];  // nested select
  alias?: string;
};

1. Basic Queries

Select All Records

const customers = await engine.find('customer');

// SQL: SELECT * FROM customer;
// MongoDB: db.customer.find({})

Select Specific Fields

const customers = await engine.find('customer', {
  fields: ['company_name', 'industry', 'annual_revenue'],
});

// SQL: SELECT company_name, industry, annual_revenue FROM customer;
// MongoDB: db.customer.find({}, { company_name: 1, industry: 1, annual_revenue: 1 })

Limit and Offset

const customers = await engine.find('customer', {
  limit: 10,
  offset: 20,  // Skip first 20, get next 10
});

// SQL: SELECT * FROM customer LIMIT 10 OFFSET 20;
// MongoDB: db.customer.find().skip(20).limit(10)

2. Filtering

Filters use the where clause with MongoDB-style $op operators (object syntax).

Implicit Equality

The simplest filter — a field-value pair implies $eq:

const query: QueryAST = {
  object: 'customer',
  where: {
    industry: 'tech',       // Implicit: { $eq: 'tech' }
  },
};

// SQL: WHERE industry = 'tech'

Explicit Operators

Use $op keys for non-equality comparisons:

// Not equal
const query: QueryAST = {
  object: 'customer',
  where: {
    status: { $ne: 'inactive' },
  },
};
// SQL: WHERE status != 'inactive'

// Greater than
const query: QueryAST = {
  object: 'customer',
  where: {
    annual_revenue: { $gt: 1000000 },
  },
};
// SQL: WHERE annual_revenue > 1000000

Supported Operators

Operator	Description	Example
`$eq`	Equal (implicit default)	`{ status: 'active' }` or `{ status: { $eq: 'active' } }`
`$ne`	Not equal	`{ status: { $ne: 'closed' } }`
`$gt`	Greater than	`{ revenue: { $gt: 10000 } }`
`$gte`	Greater or equal	`{ score: { $gte: 80 } }`
`$lt`	Less than	`{ age: { $lt: 65 } }`
`$lte`	Less or equal	`{ discount: { $lte: 20 } }`
`$in`	In list	`{ stage: { $in: ['proposal', 'negotiation'] } }`
`$nin`	Not in list	`{ status: { $nin: ['deleted', 'archived'] } }`
`$contains`	String contains	`{ name: { $contains: 'Inc' } }`
`$notContains`	String does not contain	`{ name: { $notContains: 'test' } }`
`$startsWith`	String starts with	`{ email: { $startsWith: 'admin' } }`
`$endsWith`	String ends with	`{ domain: { $endsWith: '.com' } }`
`$between`	Range (inclusive)	`{ created_at: { $between: ['2024-01-01', '2024-12-31'] } }`
`$null`	Null check	`{ manager_id: { $null: true } }` / `{ phone: { $null: false } }`
`$exists`	Field exists (NoSQL)	`{ metadata: { $exists: true } }`

Multiple Conditions (Implicit AND)

Multiple keys in where are combined with AND logic:

const query: QueryAST = {
  object: 'opportunity',
  where: {
    stage: 'Closed Won',
    amount: { $gt: 50000 },
    close_date: { $gte: '2024-01-01' },
  },
};

// SQL: WHERE stage = 'Closed Won' AND amount > 50000 AND close_date >= '2024-01-01'

Logical OR (`$or`)

const query: QueryAST = {
  object: 'contact',
  where: {
    $or: [
      { title: { $contains: 'CEO' } },
      { title: { $contains: 'President' } },
      { title: { $contains: 'Founder' } },
    ],
  },
};

// SQL: WHERE (title LIKE '%CEO%' OR title LIKE '%President%' OR title LIKE '%Founder%')

Logical AND (`$and`)

Explicit $and is useful when you need multiple conditions on the same field:

const query: QueryAST = {
  object: 'product',
  where: {
    $and: [
      { price: { $gte: 10 } },
      { price: { $lte: 100 } },
    ],
  },
};

// SQL: WHERE price >= 10 AND price <= 100

Logical NOT (`$not`)

const query: QueryAST = {
  object: 'customer',
  where: {
    $not: {
      status: { $in: ['deleted', 'suspended'] },
    },
  },
};

// SQL: WHERE NOT (status IN ('deleted', 'suspended'))

Complex Logic (AND + OR)

const query: QueryAST = {
  object: 'opportunity',
  where: {
    'account.industry': 'tech',           // AND (industry = tech)
    $or: [                                 // AND (
      { amount: { $gt: 100000 } },         //   amount > 100000
      { is_strategic: true },              //   OR is_strategic = true
    ],                                     // )
  },
};

// SQL: WHERE account.industry = 'tech'
//      AND (amount > 100000 OR is_strategic = true)

Date Filters

// Specific date
where: { created_at: '2024-01-15' }

// Date range with $between
where: { created_at: { $between: ['2024-01-01', '2024-12-31'] } }

// Comparison operators on dates
where: { due_date: { $lt: '2024-06-01' } }
where: { created_at: { $gte: '2024-01-01' } }

Null Checks

// Field IS NULL
where: { manager_id: { $null: true } }

// Field IS NOT NULL
where: { phone: { $null: false } }

// Field exists (NoSQL)
where: { metadata: { $exists: true } }

Nested Relation Filters

Filter through relationships without explicit joins:

const query: QueryAST = {
  object: 'opportunity',
  where: {
    account: {
      industry: 'tech',
      annual_revenue: { $gt: 1000000 },
    },
  },
};

3. Sorting

Sorting uses the orderBy array of SortNode objects.

Single Field Sort

const query: QueryAST = {
  object: 'customer',
  orderBy: [{ field: 'company_name', order: 'asc' }],
};

// SQL: ORDER BY company_name ASC

Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  orderBy: [
    { field: 'priority', order: 'desc' },
    { field: 'created_at', order: 'asc' },
  ],
};

// SQL: ORDER BY priority DESC, created_at ASC

const query: QueryAST = {
  object: 'contact',
  orderBy: [{ field: 'account.company_name', order: 'asc' }],
};

// SQL: ORDER BY account.company_name ASC

4. Relationships (Expand)

The expand property enables recursive loading of related records through lookup and master_detail fields. Each key is a relationship field name; the value is a nested QueryAST.

Basic Expand

const query: QueryAST = {
  object: 'opportunity',
  fields: ['name', 'amount'],
  expand: {
    account: {
      object: 'account',
      fields: ['company_name'],
    },
  },
};

// Result:
// [
//   {
//     name: 'Big Deal',
//     amount: 100000,
//     account: { company_name: 'Acme Corp' }
//   }
// ]

Multiple Relationships

const query: QueryAST = {
  object: 'opportunity',
  fields: ['name'],
  expand: {
    account: { object: 'account', fields: ['company_name'] },
    owner: { object: 'user', fields: ['name', 'email'] },
  },
};

Nested Expand (Deep Loading)

const query: QueryAST = {
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    assignee: { object: 'user', fields: ['name', 'email'] },
    project: {
      object: 'project',
      expand: {
        org: { object: 'org', fields: ['name'] },
      },
    },
  },
};

The engine resolves expand via batch $in queries (driver-agnostic) with a default max depth of 3.

Filtered Expand

Expansion follows lookup and master_detail fields — i.e. the foreign key lives on the object you are querying. The nested QueryAST can filter (where) and select (fields) the related records:

const query: QueryAST = {
  object: 'task',
  fields: ['title', 'assignee'],
  expand: {
    // assignee is a lookup → user; only resolve assignees that are still active.
    assignee: {
      object: 'user',
      where: { active: { $eq: true } },
      fields: ['name', 'email'],
    },
  },
};

The nested where is AND-merged with the batch $in the engine uses to load related records, so a related record is attached only when it also matches your filter. A foreign key whose target is filtered out is left as the raw id (unresolved) rather than dropped.

Per-parent shaping (limit / offset / orderBy) is not honored on the expand path. The engine batch-loads every parent's related records in a single $in query and then re-attaches them to each parent by foreign key, so it cannot express a per-parent page size, and the injected order follows each parent's own foreign-key value rather than the nested orderBy. To paginate or order related records, query the related object directly with its own where + orderBy + limit.

5. Aggregations

Aggregations use the aggregations array with AggregationNode objects, combined with groupBy for grouping.

Count

const count = await engine.count('customer', {
  where: { industry: 'tech' },
});

// SQL: SELECT COUNT(*) FROM customer WHERE industry = 'tech'
// Result: 42

Group By with Aggregations

const query: QueryAST = {
  object: 'opportunity',
  fields: ['stage'],
  groupBy: ['stage'],
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total_amount' },
  ],
};

// Result:
// [
//   { stage: 'Prospecting', count: 10, total_amount: 500000 },
//   { stage: 'Qualification', count: 5, total_amount: 250000 }
// ]

SQL compilation:

SELECT
  stage,
  COUNT(*) AS count,
  SUM(amount) AS total_amount
FROM opportunity
GROUP BY stage

Aggregation Functions

const query: QueryAST = {
  object: 'opportunity',
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total' },
    { function: 'avg', field: 'amount', alias: 'average' },
    { function: 'min', field: 'amount', alias: 'min_amount' },
    { function: 'max', field: 'amount', alias: 'max_amount' },
  ],
};

// Result:
// { count: 100, total: 5000000, average: 50000, min_amount: 10000, max_amount: 500000 }

Supported functions: count, sum, avg, min, max, count_distinct, array_agg, string_agg

Group By Multiple Fields

const query: QueryAST = {
  object: 'opportunity',
  fields: ['stage', 'owner_name'],
  groupBy: ['stage', 'owner_name'],
  aggregations: [
    { function: 'count', alias: 'count' },
    { function: 'sum', field: 'amount', alias: 'total' },
  ],
};

HAVING Clause

Filter groups after aggregation using the having property:

const query: QueryAST = {
  object: 'opportunity',
  groupBy: ['account_id'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' },
  ],
  having: {
    total: { $gt: 1000000 },  // Only accounts with > $1M pipeline
  },
};

// SQL: HAVING SUM(amount) > 1000000

6. Advanced Queries

Distinct

const query: QueryAST = {
  object: 'account',
  fields: ['industry'],
  distinct: true,
};

// SQL: SELECT DISTINCT industry FROM account

Full-Text Search

The search parameter configures full-text search:

const query: QueryAST = {
  object: 'article',
  search: {
    query: 'ObjectStack tutorial',
    fields: ['title', 'content', 'tags'],
    fuzzy: true,
    boost: { title: 2.0 },
  },
  limit: 10,
};

// PostgreSQL: Uses tsvector/tsquery
// MongoDB: Uses $text index

Joins

For cross-object queries beyond expand, use explicit joins:

const query: QueryAST = {
  object: 'order',
  fields: ['id', 'amount'],
  joins: [
    {
      type: 'inner',
      object: 'customer',
      alias: 'c',
      on: { 'order.customer_id': { $eq: { $field: 'c.id' } } },
    },
  ],
};

// SQL: SELECT o.id, o.amount FROM orders o
//      INNER JOIN customers c ON o.customer_id = c.id

Join types: inner, left, right, full

Window Functions

const query: QueryAST = {
  object: 'order',
  fields: ['id', 'customer_id', 'amount'],
  windowFunctions: [
    {
      function: 'row_number',
      alias: 'rank',
      over: {
        partitionBy: ['customer_id'],
        orderBy: [{ field: 'amount', order: 'desc' }],
      },
    },
  ],
};

// SQL: SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank
//      FROM orders

7. Pagination

Offset-Based Pagination

// Page 1 (records 0-9)
const page1 = await engine.find('customer', {
  limit: 10,
  offset: 0,
});

// Page 2 (records 10-19)
const page2 = await engine.find('customer', {
  limit: 10,
  offset: 10,
});

Drawback: Slow for large offsets (database still scans all skipped rows).

Cursor-Based Pagination

// First page
const result = await engine.find('customer', {
  limit: 10,
  orderBy: [{ field: 'id', order: 'asc' }],
});

// Next page (use cursor)
const nextResult = await engine.find('customer', {
  cursor: { id: result[result.length - 1].id },
  limit: 10,
  orderBy: [{ field: 'id', order: 'asc' }],
});

Advantage: Consistent performance regardless of page depth.

8. Real-World Examples

CRM: Open Opportunities

const openOpportunities = await engine.find('opportunity', {
  where: {
    stage: { $nin: ['Closed Won', 'Closed Lost'] },
    owner_id: currentUser.id,
  },
  orderBy: [{ field: 'amount', order: 'desc' }],
  fields: ['name', 'amount', 'close_date'],
  expand: {
    account: { object: 'account', fields: ['company_name'] },
  },
});

E-Commerce: Product Search

const products = await engine.find('product', {
  where: {
    is_active: true,
    inventory_qty: { $gt: 0 },
    category_id: { $in: selectedCategories },
    price: { $between: [minPrice, maxPrice] },
  },
  search: {
    query: searchTerm,
    fields: ['name', 'description'],
    fuzzy: true,
  },
  orderBy: [{ field: 'popularity_score', order: 'desc' }],
  limit: 20,
});

Analytics: Revenue by Month

const monthlyRevenue: QueryAST = {
  object: 'order',
  where: {
    status: 'completed',
    created_at: { $gte: '2024-01-01' },
  },
  groupBy: ['month'],
  aggregations: [
    { function: 'sum', field: 'total_amount', alias: 'revenue' },
    { function: 'count', alias: 'order_count' },
    { function: 'avg', field: 'total_amount', alias: 'avg_order' },
  ],
  orderBy: [{ field: 'month', order: 'asc' }],
};

9. Error Handling

Invalid Query

try {
  await engine.find('customer', {
    where: { invalid_field: 'value' },  // Field doesn't exist
  });
} catch (error) {
  // QueryValidationError: Field 'invalid_field' does not exist on object 'customer'
}

Security Violations

try {
  await engine.find('account', {
    where: { owner_id: { $ne: currentUser.id } },
  });
} catch (error) {
  // PermissionError: Access denied to object 'account'
}

Legacy Compatibility

Tuple / Array / 三元组 Syntax — UI Builder Input Only

The tuple/array format (e.g. ['status', '=', 'active']) and the filters key are legacy input formats used by some UI-layer filter builders (FilterBuilder, ObjectUI). They are not the canonical protocol format.

Before entering the ObjectQL protocol or IDataEngine, tuple filters must be converted to the canonical where + $op object format using the parseFilterAST() utility from @objectstack/spec/data:

import { parseFilterAST } from '@objectstack/spec/data';

// UI Builder output (tuple format)
const uiFilter = ['and', ['status', '=', 'active'], ['priority', '>', 3]];

// Convert to canonical format
const where = parseFilterAST(uiFilter);
// → { $and: [{ status: 'active' }, { priority: { $gt: 3 } }] }

Similarly, the following legacy field names should not be used in new code:

Legacy	Canonical	Notes
`filters` (array of tuples)	`where` (FilterCondition object)	Use `parseFilterAST()` to convert
`sort`	`orderBy`	Array of `{ field, order }` objects
`group_by`	`groupBy`	Array of field name strings
`aggregate` (object map)	`aggregations` (array)	Array of `AggregationNode` objects
`expand` (string array)	`expand` (Record)	Map of field name → nested QueryAST
`skip`	`offset`	Number
`select`	`fields`	Array of FieldNode
`populate`	`expand`	Map of field name → nested QueryAST

Schema Definition

Security

Type System

On this page