JSON syntax

The JSON syntax is isomorphic with the pipe syntax: every pipe form has a JSON equivalent, every JSON form has a pipe equivalent, and both decode into the same AST before a single compiler turns it into SQL. Nothing is expressible in one and not the other, with the small set of exceptions listed under Gaps.

{
  "from": "orders",
  "where": { "status": "paid", "total": { "$gt": 100 } },
  "include": { "user": { "select": ["name", "email"] } },
  "orderBy": ["-created_at"],
  "limit": 10
}

orders | status = "paid", total > 100 | +user(name, email) | -created_at | 10

Same tree, same SQL, same access rules.

When to reach for it

Use JSON when	Use the pipe form when
The query is assembled by code — a UI filter panel, a saved view, a generated report	The query is written by a person
It is stored, versioned or diffed as data	It appears inline in a script
A model produces it — a JSON schema constrains generation far better than a grammar	Brevity matters
Fragments are merged from several sources	—

The deciding question is who writes it. Building a pipe string by concatenation invites exactly the injection problem this DSL exists to avoid; building a JSON object is ordinary data manipulation.

Choosing the operation

The decoder picks the operation from the first key it recognises, in this order:

Key present	Operation
`from`	Query
`count`	Count query
`search` + `in`	Multi-entity search
`queries`	Multi-query
`create`	Create mutation
`update`	Update mutation
`upsert`	Upsert mutation
`delete`	Delete mutation
`transition`	Stateflow transition
`transaction`	Transaction
`operations`	Operation batch

An object with none of these is refused: unknown operation type in JSON. An object with two of them resolves by that order rather than erroring, so do not send both from and create and expect a complaint — send one.

Query keys

Key	Type	Meaning
`from`	string, or `{"$ref": "..."}`	The entity. Required
`id`	string	Fetch by primary key. Compiles to `<pk> = $1`
`where`	object	The filter. See Operators
`select`	array or object	Projection. See Projection
`include`	object or array	Relation expansion
`orderBy`	array or object	Sorting
`groupBy`	array of strings	Grouping
`having`	object	Post-aggregation filter, same grammar as `where`
`limit`	number	Row cap
`offset`	number	Offset pagination
`page`, `pageSize`	number	Page pagination
`after`, `before`	string	Cursor pagination
`traverse`	object	Hierarchy traversal
`depth`	number or object	Traversal depth
`direction`	string	`ancestors` or `descendants`
`with`	object	CTEs and subqueries
`datastore`	string	Target a non-default datastore
`options`	object	See Options

Operators

Field conditions live under where. A bare value means equality; an object selects an operator.

{ "where": { "status": "paid" } }
{ "where": { "total": { "$gt": 100 } } }

Operator	Meaning	Example
`$eq`	Equal	`{"status": {"$eq": "paid"}}`
`$ne`	Not equal	`{"status": {"$ne": "void"}}`
`$gt`, `$gte`	Greater than, or equal	`{"total": {"$gte": 100}}`
`$lt`, `$lte`	Less than, or equal	`{"total": {"$lt": 1000}}`
`$in`	Member of a set	`{"status": {"$in": ["paid", "shipped"]}}`
`$nin`	Not a member	`{"status": {"$nin": ["void", "draft"]}}`
`$like`	Pattern, case-sensitive	`{"name": {"$like": "Ada%"}}`
`$nlike`	Pattern, negated	`{"name": {"$nlike": "test%"}}`
`$ilike`	Pattern, case-insensitive	`{"email": {"$ilike": "%@example.com"}}`
`$contains`	Substring or collection membership	`{"tags": {"$contains": "urgent"}}`
`$between`	Inclusive range, two-element array	`{"total": {"$between": [100, 500]}}`
`$null`	Is null, or is not	`{"deleted_at": {"$null": true}}`
`$exists`	Field present	`{"metadata": {"$exists": true}}`

A bare value is equality, and that is the whole shorthand. There is no implicit $in for an array — {"status": ["paid","shipped"]} is not a set membership, it is an equality against an array value, which is almost certainly not what you meant. Write $in.

Combining conditions

Several fields in one object are AND-ed:

{ "where": { "status": "paid", "total": { "$gt": 100 } } }

For anything else, name the connective:

Key	Takes	Meaning
`$and`	array of expressions	All must hold
`$or`	array of expressions	At least one
`$not`	one expression	Negation

{
  "where": {
    "$or": [
      { "status": "paid" },
      { "$and": [
        { "status": "pending" },
        { "created_at": { "$lt": { "$now": "-7d" } } }
      ] }
    ]
  }
}

Nesting is unlimited, and $and / $or / $not may appear at any depth.

$or at the top of an object with sibling fields

Keys beginning with $ are handled first and the remaining fields are AND-ed separately, so

{ "where": { "$or": [ ... ], "tenant_id": "acme" } }

does not mean “(or) AND tenant_id”. Write the intent explicitly with $and.

Values

JSON	Becomes
`"text"`	String
`42`, `3.14`	Number
`true`, `false`	Boolean
`null`	Null — prefer `{"$null": true}` for the predicate
`["a","b"]`	Array, for `$in`, `$nin`, `$between`
`{"$now": "..."}`	Relative time. See below
`{"$ref": "..."}`	A reference to another query’s result

Relative time

{ "created_at": { "$gte": { "$now": "-7d" } } }

Unit	Meaning
`s`	seconds
`m`	minutes
`h`	hours
`d`	days
`w`	weeks
`M`	months — capital M
`y`	years

m is minutes and M is months, which is the one to get right. The pipe syntax’s named anchors — today, yesterday, startofweek, startofmonth, startofyear — are available in the same position.

Projection

{ "select": ["id", "total", "status"] }

With aggregation, use the object form:

{
  "from": "orders",
  "groupBy": ["status"],
  "select": { "status": true, "n": { "$count": "*" }, "revenue": { "$sum": "total" } }
}

Omitting select projects the entity’s declared fields in canonical order. It never compiles to SELECT * — which is what makes a schema change visible as a diff rather than as a surprise in a response body.

Includes

{ "include": { "user": { "select": ["name", "email"] } } }

{ "include": ["user", "line_items"] }

An include issues a separate query per relation, not a join. That is deliberate — it keeps the row count of the parent query honest — and it is why an include over a large parent set costs what it costs. The nested object accepts select, where, orderBy and limit, applied to the relation.

Ordering, grouping, pagination

{ "orderBy": ["-created_at", "name"] }
{ "orderBy": [{ "field": "created_at", "dir": "desc" }] }

A leading - is descending, a leading ^ or nothing is ascending.

Pagination	Keys	Notes
Offset	`limit`, `offset`	Simple, and drifts under concurrent writes
Page	`page`, `pageSize`	Parses; see the pipe page on what compiles
Cursor	`after`, `before`	Stable under writes. Prefer for anything user-facing

Traversal

{
  "from": "employees",
  "traverse": { "direction": "descendants", "via": "manager_id" },
  "depth": { "$lte": 3 }
}

direction is ancestors or descendants; via names the self-referencing field. depth constrains the walk and applies only to a traversal.

Options

{ "options": { "stats": true, "datastore": "reporting", "timeout": 5000 } }

Option	Effect
`stats`	Return timing and the compiled statement alongside the rows
`datastore`	Run against a named datastore
`timeout`	Milliseconds
`cache`	Cache directives
`locale`	Locale for localised fields
`unmask`, `untokenize`	Reveal protected values, subject to the caller’s rights
`version`	Read a specific version, where versioning is on
`deleted`	Include soft-deleted rows
`meta`	Include record metadata

unmask and untokenize are requests, not grants — the caller still has to be entitled to the underlying value. See Compliances.

Mutations

{ "create": "orders", "data": { "status": "draft", "total": 0 }, "returning": ["id", "created_at"] }

{ "update": "orders", "id": "abc-123", "data": { "status": "paid" }, "returning": ["id", "status"] }

{ "update": "orders", "where": { "status": "draft" }, "data": { "status": "void" } }

{ "delete": "orders", "id": "abc-123", "cascade": true }

{ "upsert": "orders", "on": ["external_id"], "data": { "external_id": "X-1", "total": 250 } }

Key	Applies to	Meaning
`data`	create, update, upsert	The values
`id`	update, delete, transition	Target one row by primary key
`where`	update, delete	Target a set
`on`	upsert	Conflict key
`returning`	all	Columns to return
`cascade`	delete	Follow declared cascades
`purge`	delete	Hard delete rather than soft

An update by id does not put the primary key in the SET clause, and a body whose id contradicts the addressed row is refused rather than silently preferring one. Both matter when a client round-trips a record it just read.

Transactions and batches

{
  "transaction": [
    { "create": "orders", "data": { "total": 0 } },
    { "create": "line_items", "data": { "order_id": { "$ref": "0.id" }, "sku": "A-1" } }
  ]
}

$ref addresses an earlier operation’s result by index and field, which is what makes a create-then-reference sequence possible without a round trip. operations is the non-transactional form of the same shape — each runs, and a failure does not roll back the others.

Gaps against the pipe syntax

Isomorphic in principle, and these are the edges where it currently is not:

Pipe form	JSON
`match`	No `$match` — use `$like` / `$ilike`
`nilike`	No `$nilike` — express as `{"$not": {"field": {"$ilike": "..."}}}`
`\| search "term"`	`search` decodes, and like the pipe form it does not compile
Comments (`--`)	JSON has none. Carry intent in a sibling key your code ignores

Errors

The decoder reports the path it was walking, which is worth reading rather than guessing at:

invalid where clause: field "total": unknown operator "$gtt"

An unknown $-operator is an error, not an ignored key. That is the opposite of the shorthand trap in the operator table above — a misspelled operator fails loudly, while a misused shape ({"status": ["paid","shipped"]}) is valid JSON meaning something you did not intend.