Query DSL

The Data API block has one query AST and three ways to write it: a pipe-oriented text DSL, a JSON DSL, and a fluent Go builder. All three decode into the same tree, and one compiler turns that tree into dialect-specific SQL.

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

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

Both compile to the same statement:

SELECT "id", "status", "total", "created_at" FROM "public"."orders"
 WHERE ("status" = $1 AND "total" > $2)
 ORDER BY "created_at" DESC LIMIT 10
-- the projection is the entity's declared fields in canonical order, never SELECT *
-- args: [paid 100]   plus one separate query for the "user" include

Where each form is accepted

Form	Accepted by	Not accepted by
Text DSL, full query	In-process callers, the block’s example applications	HTTP, GraphQL
Text DSL, expression only	In-process helpers that take a filter string (paged query, filtered delete)	HTTP, GraphQL, access rules
JSON DSL	In-process callers	HTTP, GraphQL
Go builder	Extensions and hooks compiled into a service	—

Caution

No HTTP endpoint accepts the text DSL or the JSON DSL. The REST list endpoint builds the query from query-string parameters — see the HTTP reference for that surface, and read the parameter mapping in HTTP parameters below. GraphQL exposes equality-only filters with no operator vocabulary.

There is no configuration file in which you author this DSL. Access rules — action guards and row-level security — are boolean expressions in their own vocabulary (row.*, user.*, tenant, ==, &&, in), compiled to a WHERE predicate by a separate translator; none of the syntax on this page applies there.

A standalone expression parser does exist, reachable from Go for helpers that take a filter string: it covers everything under Expressions and nothing under Pipe operations, and unlike the query parser it rejects trailing tokens.

Text DSL shape

[from] <entity>[<id-accessor>] [ "(" <field>, ... ")" ] ( "|" <pipe-op> )*

Element	Forms	Notes
Entity	orders, from orders	from is optional
By id	orders["abc-123"], orders.abc-123	Compiles to <pk> = $1
Paren projection	orders(id, total)	Shorthand for | select id, total
Pipe	| <op>	Repeatable, order-independent except for depth

Lexical rules:

Rule	Detail
Keywords	Case-insensitive (SELECT = select)
Comments	-- to end of line
Strings	Double or single quotes; escapes \n \t \r \\ \" \'
Numbers	Integer and float literals; a leading - before a digit is part of the number
Errors	line <n>, column <c>: <message> from the lexer, expected <TOKEN>, got <tok> at line <n>, column <c> from the parser

Pipe operations

Pipe	Syntax	Effect
Filter	| where <expr> or bare | <expr>	AND-ed with any earlier filter pipe
Projection	| select a, b, count() as n	See Projection
Include	| include user or | +user(name, email)	Relation expansion
Sort	| order by created_at desc	Long form; asc is the default
Sort, short	| -created_at, ^name	- descending, ^ ascending
Limit	| limit 10
Offset	| offset 20
Limit shorthand	| 10 or | 10:20	Bare integer; N:M is limit:offset
Page	| page 3 pagesize 25	Parses, never compiles — see Pagination
Group	| group by status
Having	| having count() > 5	Same grammar as a filter
Search	| search "term"	Parses, never compiles
Traverse	| descendants via manager_id / | ancestors via parent_id
Depth	| depth <= 3 or | depth 3	Applies to traverse only
Cursor	| after "<token>" / | before "<token>"
Option	| @stats, | @datastore("reporting")	See Query options

Caution

The parser stops at the first token that is not | and does not check for end of input. orders | limit 10 offset 20 silently yields LIMIT 10 with no offset and no error. Write orders | limit 10 | offset 20 or orders | 10:20.

Expressions

Precedence, lowest to highest: OR → AND → NOT → primary (a parenthesised expression or one comparison).

Construct	Text	JSON
AND	a = 1 and b = 2, a = 1, b = 2	{"$and":[{...},{...}]} or sibling keys in one where object
OR	a = 1 or b = 2	{"$or":[{...},{...}]}
NOT	not a = 1	{"$not":{...}}
Grouping	(a = 1 or b = 2)	Object nesting

Caution

The comma is AND at the same precedence as the keyword and, and not binds only to the following primary. a = 1, b = 2 or c = 3 parses as (a = 1 AND b = 2) OR c = 3, and not a = 1 and b = 2 as (NOT a = 1) AND b = 2. Parenthesise anything mixed.

Operators

Every operator, its spelling in each front-end, and the SQL it emits.

Operator	Text DSL	JSON DSL	Emitted SQL
Equal	f = v	{"f":v} or {"f":{"$eq":v}}	"f" = $1
Not equal	f != v	{"f":{"$ne":v}}	"f" <> $1
Greater	f > v	{"f":{"$gt":v}}	"f" > $1
Less	f < v	{"f":{"$lt":v}}	"f" < $1
Greater or equal	f >= v	{"f":{"$gte":v}}	"f" >= $1
Less or equal	f <= v	{"f":{"$lte":v}}	"f" <= $1
In	f in ["a","b"]	{"f":{"$in":["a","b"]}}	"f" IN ($1, $2)
Not in	f not in ["a"]	{"f":{"$nin":["a"]}}	"f" NOT IN ($1)
Like	f like "%x%"	{"f":{"$like":"%x%"}}	"f" LIKE $1
Not like	f not like "%x%"	{"f":{"$nlike":"%x%"}}	"f" NOT LIKE $1
Case-insensitive like	f ilike "%x%"	{"f":{"$ilike":"%x%"}}	"f" ILIKE $1 on PostgreSQL
Not ilike	—	—	"f" NOT ILIKE $1; no spelling and no constructor — only by setting the operator on an AST node directly
Between	f between 20 and 60	{"f":{"$between":[20,60]}}	"f" BETWEEN $1 AND $2, inclusive
Contains	f contains "x"	{"f":{"$contains":"x"}}	"f" @> $1 on PostgreSQL (array/JSON containment)
Is null	f is null	{"f":{"$null":true}}	"f" IS NULL
Is not null	f is not null	{"f":{"$null":false}}	"f" IS NOT NULL
Exists	f exists	{"f":{"$exists":true}}	"f" IS NOT NULL
Not exists	f not exists	{"f":{"$exists":false}}	"f" IS NULL
Full-text match	—	—	Backend hook; no DSL spelling in either form

Operand type rules in the JSON DSL are enforced at decode time: $in/$nin require an array, $between requires exactly two elements, $like/$nlike/$ilike require a string, $null/$exists require a boolean. An unrecognised $-prefixed key fails with unknown operator: <key>.

Caution

exists is a NULL test, not a key-existence test — it is $null inverted. It does not test for the presence of a JSON key or a sparse attribute. There is no JSON-path operator in either DSL.

Caution

An empty IN list compiles to IN (), which every backend rejects at execution time. Nothing in the parser, decoder, or compiler guards against it — check for an empty list before you build the query.

Values

Value	Text	JSON
String	"paid", 'paid'	"paid"
Integer / float	100, 9.99	100, 9.99
Boolean	true, false	true, false
Null	null	null
List	["a", "b"]	["a","b"]
Relative time	@now - 7d	{"$now":"-7d"} or {"$now":true}
CTE reference	$active.id	{"$ref":"active"}
Named parameter	—	{"$param":"name"}
Raw SQL	—	{"$expr":"now()"}
Column reference	bare identifier: status = active	—

Caution

A bare unquoted identifier on the right of a comparison is a column reference, not a string. status = active becomes a column-to-column comparison: it compiles to "status" = "active" when active is a declared column, and otherwise aborts with field "active" not found on entity "<e>". Always quote string literals.

$param emits a placeholder and binds a literal NULL; there is no binding step, so the value is always NULL. $expr renders its contents into the statement verbatim with no validation — the shipped safety profile permits it, and it is reachable only from in-process callers, never over HTTP. Treat any caller-authored $expr as an injection surface.

Relative time

Anchors: @now, @today, @yesterday, @startofweek, @startofmonth, @startofyear. Offsets are [+|-]<int><unit>.

Unit	Meaning
s	seconds
m	minutes
h	hours
d	days
w	weeks
M	months (upper case)
y	years

customer | createdon > @now - 30d | select customer_name, customer_type, territory

Relative time is inlined into the statement as interval arithmetic (NOW() - INTERVAL '30 day'), not bound as a parameter.

Caution

Spaces around the sign are required: @now - 7d parses, @now-7d fails with unknown time anchor: now-7d. The month unit is upper-case M; lower-case m is minutes.

Field paths

A field path is dot-separated identifiers. Single-part names are validated against the entity’s declared fields plus the system columns (primary key, createdon, createdby, updatedon, updatedby, deletedon, deletedby, version), plus any projection alias or GROUP BY column tracked during compilation. An unknown column aborts compilation with field "<f>" not found on entity "<e>" before any SQL is emitted.

Caution

A dot path does not join. orders | user.country = "US" compiles to WHERE "user.country" = $1 — the whole path is quoted as one identifier and the statement fails on an unknown column. Filter on the local foreign-key column, or use an include.

Projection and aggregation

Form	Text	JSON
Field list	| select id, total, status	"select":["id","total","status"]
Paren shorthand	orders(id, total)	—
Object field	—	"select":{"status":true}
Alias	| select total as amount	—
Count all	count(), count(*)	{"n":{"$count":true}}
Count column	count(status)	—
Sum	sum(total) as revenue	{"revenue":{"$sum":"total"}}
Average	avg(standard_rate) as avg_rate	{"avg_rate":{"$avg":"standard_rate"}}
Min	min(standard_rate) as min_rate	{"min_rate":{"$min":"standard_rate"}}
Max	max(standard_rate) as max_rate	{"max_rate":{"$max":"standard_rate"}}
Group	| group by status	"groupBy":["status"]
Having	| having count() > 5	"having":{...}

In the JSON object form the key is the output alias and the value selects the field or aggregate. Aliases become legal identifiers in HAVING and ORDER BY, as do GROUP BY columns.

orders | group by status | select status, count() as n, sum(total) as revenue

SELECT "status", COUNT(*) AS "n", SUM("total") AS "revenue"
  FROM "public"."orders" GROUP BY "status"

With no projection the compiler emits every field in the entity’s canonical order rather than SELECT *; it falls back to * only when the schema registered no fields. Adding a field to an entity therefore widens the default projection.

Note

There is no SELECT DISTINCT. select distinct status parses distinct as a column name — the query then fails with field "distinct" not found, and status is never read. COUNT(DISTINCT col) exists in the AST but neither DSL can set it, and SUM/AVG/MIN/MAX ignore the flag.

Sorting

Form	Text	JSON
Long	| order by created_at desc, name asc	"orderBy":[{"field":"created_at","dir":"desc"}]
Short	| -created_at, ^name	"orderBy":["-created_at","^name"]
Default direction	ascending	ascending

In the JSON object form only the exact string desc means descending; every other value is treated as ascending.

When you supply no sort and the query is not an aggregate or GROUP BY, the compiler injects one: the entity’s declared default order if there is one, otherwise <primary key> ASC. Every list statement therefore carries an ORDER BY you did not write, which is what makes LIMIT without a sort return deterministic pages. A before cursor with no after flips every injected direction.

Pagination

Offset pagination

Form	Text	JSON
Limit	| limit 10, | 10	"limit":10
Offset	| offset 20	"offset":20
Both	| 10:20	"limit":10,"offset":20

limit and offset are emitted as literals in the statement (LIMIT 10 OFFSET 20), not as bind arguments. Both must be JSON numbers in the JSON DSL; a string fails with invalid limit: expected number, got string.

Caution

page and pagesize parse into the AST and are never compiled — no LIMIT, no OFFSET, the query returns everything up to the row cap. Use limit/offset. (The HTTP layer does its own offset = (page-1)*limit conversion, so ?page= works over REST while page in the DSL does not.)

Cursor pagination

after and before take an opaque token. Send it back exactly as received.

Form	Text	JSON
Forward	| after "<token>"	"after":"<token>"
Backward	| before "<token>"	"before":"<token>"

The token is base64url of a JSON object holding the sort-key tuple of the last returned row — one entry per ORDER BY column, in order, each tagged with the direction it was sorted in:

{"v":1,"f":[{"n":"created_at","v":"2026-05-12T10:00:00Z","d":"desc"},
            {"n":"id","v":"u-42","d":"asc"}]}

Codec version is 1. The decoder rejects any other version, malformed base64 or JSON, and an empty field list. A cursor whose field names, directions, or arity differ from the current ORDER BY is incompatible. Minting a cursor requires the sort key to be present in the returned row — project it away and the token cannot be built.

The keyset predicate is always emitted as an OR-chain, never as a SQL row-value comparison:

-- ORDER BY id ASC, after:
(("id" > $1))

-- ORDER BY created_at ASC, id ASC, after:
(("created_at" > $1) OR ("created_at" = $2 AND "id" > $3))

Per-field operator: ascending + after → >, ascending + before → <, descending + after → <, descending + before → >. Each equality prefix rebinds its value, so an n-field cursor produces n(n+1)/2 bind arguments.

Caution

A cursor that does not match the current sort is not rejected. If the token fails to decode, or the query has no ORDER BY, or the sort changed between pages, the compiler falls back to <primary key> > $n and binds the entire token string as the primary-key value. The query succeeds and returns a wrong — usually empty — page. Always send after/before with the exact sort that produced them.

The row cap

The execution engine injects a limit of 10,000 rows when a request sets none, and it also lowers a caller limit that exceeds the cap. A query that hits the cap looks like a complete result set — there is no marker in the rows. Only operator-trusted callers (bulk export, migration discovery) can disable it.

Includes

Relation expansion. Each include is a separate statement run after the parent and filtered by child_key IN (<parent key values>), then grouped back onto the parent rows.

Option	Text	JSON
Relation	| +user, | include user	"include":{"user":true}
Projection	+user(name, email)	"include":{"user":{"select":["name","email"]}}
Filter	+items(status = "open")	"include":{"items":{"where":{"status":"open"}}}
Sort	+items(-created_at)	"include":{"items":{"orderBy":["-created_at"]}}
Limit	+items(5)	"include":{"items":{"limit":5}}
Nested	+user(+org(tier))	"include":{"user":{"include":{"org":true}}}
Several	| +user, +items	"include":{"user":true,"items":true}

Inside (...) the parser distinguishes by lookahead: a bare identifier is a projected field, an identifier followed by a comparison operator is a filter, -/^ is a sort, a bare integer is the limit, and + starts a nested include.

Key direction is normalised per relation kind: has_many and has_one join parent local key to child foreign key; belongs_to joins parent foreign key to child local key.

Caution

An include limit caps the whole child statement, not rows per parent — a limit of 5 across 100 parents leaves most parents empty. Include nesting and fan-out have no ceiling, and include.<relation>.depth and options.depth are parsed and then ignored. many_to_many includes ignore the junction table and behave as belongs_to.

Note

When you combine an explicit projection with includes, the parent primary key is prepended to the projection and the child foreign key to each include’s projection, so rows can be stitched. Response keys are wider than you asked for.

Hierarchy traversal

employees["ceo-123"] | descendants via manager_id | depth <= 3

{"from":"employees","id":"ceo-123",
 "traverse":{"direction":"descendants","via":"manager_id","maxDepth":3}}

Key	Values	Notes
direction	descendants, ancestors	Anything else fails with invalid direction: <x>
via	self-referencing FK field	e.g. manager_id, parent_id
maxDepth	integer	Omitted means unbounded recursion
select	array or object	May project the virtual columns

This is the only recursive shape in the language. It compiles to WITH RECURSIVE:

WITH RECURSIVE "tree" AS (
  SELECT t.*, 0 AS "$depth", CAST(t."id" AS TEXT) AS "$path"
    FROM "public"."employees" t WHERE t."id" = $1
  UNION ALL
  SELECT r.*, "tree"."$depth" + 1,
         "tree"."$path" || '/' || CAST(r."id" AS TEXT)
    FROM "public"."employees" r
    INNER JOIN "tree" ON "tree"."id" = r."manager_id"
   WHERE "tree"."$depth" < 3
) SELECT * FROM "tree"

Two virtual columns are injected into the CTE and are selectable: $depth (0-based) and $path (/-joined primary keys). The query’s id and filter become the seed predicate. Descendants join tree.pk = child.via; ancestors join parent.pk = tree.via. The depth pipe only applies to a traversal — it does nothing on a plain query.

CTEs and subqueries

$active = users | status = "active"
orders | user_id in [$active.id]

{"with":{"active":{"from":"users","where":{"status":"active"}}},
 "from":"orders","where":{"user_id":{"$in":[{"$ref":"active"}]}}}

Named CTEs are emitted as WITH "name" AS (...) in sorted name order, so the generated SQL is deterministic. {"$ref":"<name>"} in a value position renders as a quoted identifier. An IN-subquery form exists in the AST and compiles, but no DSL syntax produces it — inline text subqueries such as user_id in (users | status = "active") do not parse.

Caution

Two shapes produce an empty table reference and invalid SQL: a text query that declares CTEs with no trailing main query, and "from":{"$ref":"<cte>"} — that form decodes, but no compiler reads it. Always follow $name = ... declarations with the query that uses them, and keep the main query’s from on a real entity.

Count

Form	Text	JSON
Count	count customer	{"count":"customer"}
Filtered	count orders | status = "paid"	{"count":"orders","where":{"status":"paid"}}

Both emit SELECT COUNT(*) FROM .... In the JSON form only where is honoured — limit, offset, and orderBy on a count request are dropped by the decoder. The text form accepts any pipe operation.

Query options

Text form is @name or @name(value); JSON form is the options object.

Option	Text	Type	Effect
datastore	@datastore("reporting")	string	Names a datastore; parsed, never read
deleted	@deleted	bool	Includes soft-deleted rows — the only option that changes the emitted SQL
unmask	@unmask	bool	Requests unmasking of masked fields, subject to permission checks
untokenize	@untokenize	bool	Requests reversal of tokenized fields
stats	@stats	bool	Returns execution statistics
meta	@meta	bool	Returns entity metadata
states	@states	bool	Returns stateflow state information
allowedactions	@allowedactions	bool	Returns permitted state transitions
delta	@delta	bool	Returns changed fields only
sendhash	@sendhash	bool	Returns content hashes for file fields
locale	@locale("fr")	string	Localisation hint
version	@version(3)	int	Record version for versioned reads
depth	@depth(2)	int	Default include depth — parsed, not applied
timeout	@timeout("30s")	string	Advisory; the engine has its own per-request timeout
cache	@cache("5m")	string	Advisory cache hint

Only deleted changes the statement, and only unmask, untokenize, stats, and meta travel further, into the compiled query’s post-processing config. Nothing reads the rest — including datastore. Over HTTP, set the equivalent request flag instead; datastore selection is ?datastore=.

What the compiler adds to your query

Between the AST and SQL emission the engine runs a fixed pipeline: AST-shape and identifier validation, an access check for the read action, row-level-security evaluation, then SQL emission and error sanitisation. Three things end up in your WHERE clause that you did not write:

Injected	When	Detail
Soft-delete predicate	Entity declares soft delete and deleted is not set	<column> IS NULL, degrading to (col IS NULL OR col = '') when only a deleted-by column is configured
Access-rule predicate	Entity has a row-level-security rule	AND-merged into the filter, flattening nested ANDs
Default sort	No explicit sort, no aggregate or GROUP BY	Entity default order, else primary key ascending

Caution

Row-level security fails closed. An untranslatable rule denies every row rather than erroring, so an empty result set can mean “denied”, not “no matching data”. Create mutations get no predicate injection — there are no rows to filter — so a create is gated by the access check plus a create-time rule evaluation over the payload, not by the WHERE clause.

Caps and refusals

Cap	Value	Behaviour past the cap
Rows per query	10,000 by default	Limit silently injected or lowered
Expression nesting depth	64	Compilation refused; the bound is inclusive, so 64 passes and 65 fails
Identifier length (PostgreSQL)	63 bytes	Compilation refused
Include nesting / fan-out	none	Each level is another statement
Traversal depth	none unless maxDepth is set	Unbounded recursion

Identifiers must be non-empty, free of NUL bytes, and free of leading and trailing whitespace. The rule is applied to every field, projection, sort, group, relation, and returning name before quoting.

The safety guard refuses three further shapes:

Refused	Condition
Filter on an encrypted column	The field uses randomised encryption and does not declare searchable, deterministic encryption, or a blind index. Checked in WHERE and HAVING only — sorting or grouping on that column is not refused
Aggregation over a compliance-tagged column	The column carries an encryption, redaction, masking, tokenization, hashing, or PII/PHI/PCI/GDPR tag and the caller lacks unmask permission; COUNT(*) is always allowed
Unknown column	Caught before emission, because unknown columns crash some drivers

Backend differences

Four dialects ship — PostgreSQL, DuckDB, ClickHouse, SQLite — and every divergent fragment (quoting, placeholders, LIKE/containment tokens, limit-offset, null ordering, CTEs, full-text, upsert) routes through a dialect contract. Each declares its capabilities:

Backend	Recursive CTE	Upsert	Updates / deletes	Full-text	Array ops
PostgreSQL	yes	yes	full	yes	yes
DuckDB	yes	yes	full	no	yes
SQLite	yes	yes	full	no	no
ClickHouse	no	no	none	no	yes

Caution

Those declarations are not enforced. Nothing refuses a recursive-CTE or upsert query on a backend that cannot run it — the failure surfaces as a raw database error at execution time. Tenant datastores on the deployable service run PostgreSQL.

HTTP parameter mapping

The REST list endpoint builds the AST from query-string parameters. Full route and response detail lives in the HTTP reference; this is the mapping to the concepts above.

Parameter	Maps to
select, fields	Projection, comma-separated; select wins
orderby, order_by	Sort, comma-separated; -field, or trailing desc / asc
limit, pagesize	Limit; limit wins
offset, page	Offset; with no offset, offset = (page-1) * limit
after, before	Cursors, passed through verbatim
include	Relations, comma-separated, default projection only
groupby	GROUP BY, comma-separated
filter	Legacy ?filter=<key>=<value>, split on the first =, AND-ed as an equality
any unreserved key	field = '<value>', all AND-ed together

GET /rest/orders?status=paid&select=id,total,status&orderby=-created_at&limit=10&include=customer,items
GET /rest/orders?orderby=-created_at,id&limit=25&after=<opaque-cursor>

Caution

HTTP filters are string-typed equality only. ?total=100 binds the string '100', and there is no query-string syntax for >, <, IN, LIKE, OR, or NULL tests. ?having= and ?depth= are accepted and ignored. The POST /rest/{entity}/search body is likewise flat equality — a nested operator object is stringified, not interpreted — and a free-text query in that body returns 501.

Parses but does not run

These are accepted by one or both front-ends and then dropped without an error. Do not build on them.

Feature	Behaviour
search / free-text	Populates the AST, never compiles; the query returns unfiltered rows
Multi-entity search (search "x" in [a, b])	Produces an empty FROM and invalid SQL
page / pagesize	No LIMIT, no OFFSET
Include depth, options.depth	No effect on the emitted statements
options.datastore / @datastore	Never read; datastore selection is the ?datastore= request flag
"from":{"$ref":"<cte>"}	Decodes, then emits an empty table reference and invalid SQL
Explicit join / left join	Reserved words with no grammar; a field named join is unusable
Dot-path filters	Quoted as a single identifier, no join
SELECT DISTINCT	No AST field, no syntax
Multi-query envelope	Decodes, but no compiler accepts it — iterate the map yourself
Update operators $push, $pull, $unset	Declared, silently produce no assignment
Bulk create from an array payload	Parses, then rejected by the insert compiler
transition steps	Parse, then rejected by the SQL compiler — they run on the stateflow path

Mutations

Mutations share the AST, the pipeline, and the operator vocabulary. Create, update, upsert, delete (soft and hard), and restore compile to SQL; RETURNING accepts a field list, and the single entry "*" expands to every field in canonical order.

Data operator	JSON	Emitted
Increment	{"stock":{"$incr":1}}	"stock" = "stock" + :stock
Decrement	{"stock":{"$decr":1}}	"stock" = "stock" - :stock
Now	{"seen":{"$now":true}}	"seen" = NOW()
Raw	{"c":{"$expr":"..."}}	Verbatim fragment

{"update":"orders","where":{"id":"x"},"data":{"stock":{"$decr":1},"seen":{"$now":true}}}

UPDATE "public"."orders" SET "seen" = NOW(), "stock" = "stock" - :stock WHERE "id" = $1

Reads and WHERE clauses use positional placeholders; insert values and update assignments use named placeholders (:column). A soft delete compiles to an UPDATE that stamps the delete markers, so its RETURNING gives the post-image — there is no pre-image on the soft path. A hard delete (purge) returns the row as it last existed.

Reference queries

Real strings, executed against a live database by the block’s own example application — the same patterns in each form.

company | select id, name, abbr, default_currency, country
item | is_stock_item = true | select item_code, item_name, standard_rate, valuation_method
count customer
sales_order | select id, customer_id, status, priority | -createdon | 5
customer | group by customer_type | select customer_type, count() as cnt
sales_order | status in ["draft", "submitted"] | select id, status, priority
item | item_name like "%Widget%" | select item_code, item_name, standard_rate
customer | createdon > @now - 30d | select customer_name, customer_type, territory
item | standard_rate between 20 and 60 | select item_code, item_name, standard_rate
item | barcode is null | select item_code, item_name, barcode
customer | customer_type = "company", (territory = "North America" or territory = "Europe") | select customer_name, customer_type, territory
item | select avg(standard_rate) as avg_rate
item | select min(standard_rate) as min_rate, max(standard_rate) as max_rate
account | is_group = false | select account_number, account_name, account_type, balance_direction | ^account_number

{"from":"company","select":["id","name","abbr","default_currency","country"]}
{"from":"item","where":{"is_stock_item":true},"select":["item_code","item_name","standard_rate","valuation_method"]}
{"count":"customer"}
{"from":"sales_order","select":["id","customer_id","status","priority"],"orderBy":["-createdon"],"limit":5}
{"from":"customer","groupBy":["customer_type"],"select":{"customer_type":true,"count":{"$count":true}}}
{"from":"sales_order","where":{"status":{"$in":["draft","submitted"]}},"select":["id","status","priority"]}
{"from":"item","where":{"item_name":{"$like":"%Widget%"}},"select":["item_code","item_name","standard_rate"]}
{"from":"item","where":{"standard_rate":{"$between":[20,60]}},"select":["item_code","item_name","standard_rate"]}
{"from":"item","where":{"barcode":{"$null":true}},"select":["item_code","item_name","barcode"]}
{"from":"customer","where":{"$and":[{"customer_type":"company"},{"$or":[{"territory":"North America"},{"territory":"Europe"}]}]},"select":["customer_name","customer_type","territory"]}