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

import { Search, Filter, GitMerge, BarChart } from 'lucide-react';


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:**
```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):**
```typescript
import type { QueryAST } from '@objectstack/spec';

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`:

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

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

```typescript
// 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

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

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

### Select Specific Fields

```typescript
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

```typescript
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`:

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

// SQL: WHERE industry = 'tech'
```

### Explicit Operators

Use `$op` keys for non-equality comparisons:

```typescript
// 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:

```typescript
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`)

```typescript
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:

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

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

### Logical NOT (`$not`)

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

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

### Complex Logic (AND + OR)

```typescript
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

```typescript
// 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

```typescript
// 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:

```typescript
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

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

// SQL: ORDER BY company_name ASC
```

### Multiple Fields

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

// SQL: ORDER BY priority DESC, created_at ASC
```

### Sort on Related Fields

```typescript
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

```typescript
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

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

### Nested Expand (Deep Loading)

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

<Callout type="info">
The engine resolves `expand` via batch `$in` queries (driver-agnostic) with a default max depth of 3.
</Callout>

### Filtered Expand

You can filter, sort, and limit expanded relations:

```typescript
const query: QueryAST = {
  object: 'account',
  fields: ['company_name'],
  expand: {
    opportunities: {
      object: 'opportunity',
      where: { stage: { $ne: 'Closed Lost' } },
      orderBy: [{ field: 'amount', order: 'desc' }],
      limit: 5,
    },
  },
};
```

---

## 5. Aggregations

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

### Count

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

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

### Group By with Aggregations

```typescript
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:**
```sql
SELECT
  stage,
  COUNT(*) AS count,
  SUM(amount) AS total_amount
FROM opportunity
GROUP BY stage
```

### Aggregation Functions

```typescript
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

```typescript
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:

```typescript
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

```typescript
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:

```typescript
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:

```typescript
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

```typescript
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

```typescript
// 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

```typescript
// 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

```typescript
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

```typescript
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

```typescript
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

```typescript
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

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

---

## Legacy Compatibility

<Callout type="warn">
**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`:

```typescript
import { parseFilterAST } from '@objectstack/spec';

// 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 |
</Callout>

---

## Next Steps

<Cards>
  <Card
    title="Schema Definition"
    href="/docs/protocols/objectql/schema"
    description="Learn object and field configuration"
  />
  <Card
    title="Security"
    href="/docs/protocols/objectql/security"
    description="Implement access control and permissions"
  />
  <Card
    title="Type System"
    href="/docs/protocols/objectql/types"
    description="Master all field types and relationships"
  />
</Cards>
