PureQL Specification

PureQL is a JSON-based declarative query language for relational data. Queries are plain JSON objects validated against a JSON Schema, so they are easy to generate programmatically, serialize, transmit and validate without a parser.

The design goal is a type system enforced by the schema itself. Where an expression may appear, what type it has, how nulls propagate and which columns a query returns are all checked by any JSON Schema 2020-12 validator. The interpreter is left with name resolution and the few checks listed under Validation, none of which needs type inference. Semantics are defined by this specification, not by any particular SQL dialect or host language.

PureQL-Specification.json is generated by tools/generate_schema.py; see Tooling.

Quick Reference

Property	Required	Description
subqueries	no	Named helper queries the main query can read from. See Subqueries
from	yes	Source to read: { "entity": … } or { "subquery": … }, with an optional alias
joins	no	Further sources, each with a join type and an on condition. Omit rather than leave empty
where	no	Row filter, before grouping
groupBy	no	Group keys, each { alias?, type, expression } with any row expression
having	no	Group filter; only with groupBy
select	yes	Result columns, each { alias, type, expression }
orderBy	no	Sort keys, each { expression, direction }. Omit rather than leave empty
distinct	no	When true, remove duplicate result rows
pagination	no	{ skip, take }, each a number or an integer parameter

A query with groupBy is a grouped query; without it, a plain query. The two have different rules for select, having and orderBy (see Contexts).

---

Type System

Types

Type	Literal value	Example
integer	JSON integer: a number with no fractional part, so 5.0 counts. The schema sets no range	42
decimal	any JSON number, an integer or an exponent included	19.99
string	JSON string	"active"
boolean	true / false	true
date	YYYY-MM-DD, a real calendar date (February 29 only in leap years)	"2024-01-31"
time	hh:mm:ss[.fraction]: hours 00–23, seconds 00–59, 1 to 9 fractional digits	"18:30:00.125"
datetime	a date, T, a time, then an offset (see Temporal types)	"2024-01-31T18:30:00+03:00"
uuid	8-4-4-4-12 hexadecimal digits, either case, no braces or prefix	"3f2a6c1e-8b4d-4e2a-9c1f-1a2b3c4d5e6f"

Every type is either non-null or nullable:

{ "name": "decimal" }                    // non-null
{ "name": "decimal", "nullable": true }  // nullable

Null

There is no null type. A null literal always carries its type, and a literal is nullable exactly when its value is null:

{ "type": { "name": "uuid", "nullable": true }, "value": null }

Invariant: the type of every expression is determined by its subtree and its context (row, projection or group, fixed by its position), never inferred from its surroundings. The context matters only for an aggregate without over, whose rows and so whose nullability depend on whether the query is grouped. With implicit conversions this is the narrowest type: an integer expression is also a valid decimal.

Implicit conversions

From	To
T	T nullable
integer	decimal (and integer? to decimal?)

The second row also lets integer and decimal values meet in equal, comparisons and in, and an integerList stand where a list of decimal is expected. There are no other conversions. date and datetime are not interchangeable, and nothing converts to string implicitly.

Null semantics

PureQL uses the following null semantics:

• Lifted operators: arithmetic (integerDivide and modulo included), concat, date/time math, round, floor and ceiling. A nullable operand makes the result nullable, and the result is null when any operand is null.
• if: the result is nullable when either branch is nullable, and its value is the value of the branch taken, so if(true, 1, null) is 1.
• Comparisons: equal, notEqual, ordering comparisons and in accept nullable operands and return a non-null boolean. null == null is true, and any ordering comparison against null is false. A null value is in a list exactly when the list contains null, which only a nullable subquery column can. This holds in every clause, join.on included.
• Conditions: where, having, join.on, and / or / not, if.condition and aggregate predicate require a non-null boolean. To use a nullable boolean as a condition, write equal(x, true) or coalesce(x, false).
• coalesce: non-null as soon as one operand is non-null.
• Sorting: null sorts before every value in ascending order and after every value in descending order.

Numbers

Operation	Result
add, subtract, multiply	integer when every operand is integer, otherwise decimal
divide	always decimal. There is no truncating division by accident
integerDivide, modulo	integer; the operands must be integer
floor, ceiling, round	integer. round with digits gives decimal

Range, precision, rounding, integerDivide / modulo signs and division by zero are defined under Integer and decimal arithmetic.

Temporal types

• date is a local calendar date, and time a local time of day. Neither has an offset.
• datetime is an instant, a point on the global timeline:
  • The offset is mandatory: Z or ±hh:mm up to ±23:59, with T and Z in upper case.
  • -00:00 is rejected: in RFC 3339 it means "offset unknown", which is a naive timestamp under another name. +00:00 is the same as Z.
  • Comparison, equal, *DiffSeconds, min / max, orderBy, groupBy and distinct all work on the instant, so 03:00+03:00 equals 00:00Z. The offset only affects how a value is written, and datetime results are returned in UTC, written with Z.
  • A datetime parameter bound without an offset is an error. If storage holds naive timestamps, how they map to instants is interpreter configuration and is never guessed.

Literal patterns use [0-9], never \d, no lookahead, and reject newlines explicitly. Python's re treats \d as any Unicode digit and lets $ match before a final newline, while ECMA-262 does neither, and lookahead support varies, so these rules keep every validator in agreement.

---

Building Blocks

Node	Shape	Notes
Field	{ "source": "orders", "field": "total", "type": { "name": "decimal" } }	source is an entity, a subquery or an alias of either
Literal	{ "type": { "name": "string" }, "value": "active" }	Always non-null unless value is null
Parameter	{ "param_name": "since", "type": { "name": "datetime" } }	Bound at execution time; may be nullable
List	{ "type": { "name": "stringList" }, "value": ["a", "b"] }, a list parameter { "param_name": …, "type": { "name": "stringList" } }, or a subquery column	A value, not a column. Accepted only by in. A list literal holds no null, and a list type is never nullable. Every type T has TList
Group key	{ "key": 0, "type": { "name": "uuid" } }	Zero-based index into groupBy, repeating that key's declared type; only in grouped select, having and orderBy
Operator	{ "operator": "add", "values": [ … ] }	See Operators

Fields, parameters and group keys declare their type at the point of use, including nullability, and the declaration must match exactly. For example, a field read from the optional side of an outer join is declared nullable, and a non-null field elsewhere is declared non-null.

Names of entities, fields, aliases, parameters and subqueries are non-empty, have no leading or trailing space or tab, and contain no line break. Inner spaces and any other characters are allowed, e.g. "order items".

---

Contexts

There is one set of operators. Where an expression may appear is decided by its context, which is fixed by its position in the query:

Context	Used in	Fields	Group keys	Aggregates
row	where, join.on, groupBy keys, aggregate selector / predicate	yes	no	no
projection	select / orderBy of a plain query	yes	no	over all rows (the default)
group	select / having / orderBy of a grouped query	no	yes	over the group (the default) or over: "all"

Consequences, all enforced by the schema:

• Aggregates cannot appear in where or join.on, and cannot nest: an aggregate body is in row context.
• A grouped query cannot select, filter on or sort by a bare field. Use a group key or an aggregate.
• Group keys exist only in grouped queries.

---

Query Clauses

A query runs in this order: from and joins, where, groupBy, having, select, distinct, orderBy, pagination. Aggregates over all rows see the rows left by where. Each step is defined under Evaluation.

from and joins

"from":  { "entity": "orders", "alias": "o" },
"joins": [
  { "type": "left", "entity": "coupons",
    "on": { "operator": "equal",
            "left":  { "source": "o",       "field": "coupon_id", "type": { "name": "uuid", "nullable": true } },
            "right": { "source": "coupons", "field": "id",        "type": { "name": "uuid" } } } }
]

• A source names exactly one of entity or subquery, plus an optional alias. Aliases allow joining the same source twice (self-join).
• Join types: inner, left, right, full.
• on is a non-null boolean in row context.
• Nulls in on. on has the same null semantics as every other clause, so equal on two nullable keys pairs the rows whose keys are both null. This differs from SQL =. To match only non-null keys, add notEqual(key, null), as in 33_join_on_nullable_key.json. A join where either key is non-null, such as a foreign key to a primary key, is unaffected.
• Translating to SQL. equal with at least one non-null operand is plain =. With two nullable operands it is IS NOT DISTINCT FROM, which some databases cannot use for hash joins or indexes, so a notEqual(key, null) guard also lets the interpreter emit =.
• Nullability after an outer join. A field is nullable at a point of the query if it is stored nullable, or if its source is on the optional side of an outer join that precedes that point: the joined source for left, every earlier source for right, both for full.
  • A join's own on precedes the join itself, so it sees the joined source's real rows. In the example, coupons.id is non-null in its own on and nullable everywhere after it.
  • The on of a later join, where, groupBy, select and every later clause come after the join. A field that is non-null in storage is therefore nullable in the next join's on if its source was joined with left before, as in 37_outer_join_chain.json.
  • An on is never affected by joins that come after it: a right join makes the earlier sources nullable only from that join on.
  • References declare exactly this nullability, no more and no less: declaring a non-null field nullable is an error too.

where

A non-null boolean in row context, evaluated per row after the joins. A boolean field is a condition on its own.

groupBy and group keys

groupBy lists one or more keys:

{ "alias": "days_to_ship", "type": { "name": "integer", "nullable": true },
  "expression": { "operator": "dateDiffDays",
                  "left":  { "source": "orders", "field": "shipped_date", "type": { "name": "date", "nullable": true } },
                  "right": { "source": "orders", "field": "order_date",   "type": { "name": "date" } } } }

• Expression. A key may be any row expression, including a computed one such as a dateDiffDays or an if bucket.
• Type. The type is required and checked against the expression exactly like a select column. Here shipped_date is null until an order ships, so the lifted dateDiffDays and the key are integer?, and unshipped orders form one group with a null key.
• Alias. alias is optional and only names the key for readers; nothing refers to it.
• Referencing. Keys are referenced by zero-based index as { "key": i, "type": … }, and the reference repeats the key's declared type exactly. Matching the two is therefore a plain lookup.
• Use. A key behaves like an ordinary value of its type: it can be selected, compared, used in arithmetic and sorted.
• Result. Keys are not added to the result automatically; select the ones you need.

having

A non-null boolean in group context. It requires groupBy.

select

Every column declares its alias and type:

{ "alias": "revenue", "type": { "name": "decimal" },
  "expression": { "operator": "sum",
                  "selector": { "source": "orders", "field": "total", "type": { "name": "decimal" } } } }

The schema checks the expression against the declared type, allowing the implicit conversions: an integer expression fits a decimal column, and a non-null expression fits a nullable column. So every query has a declared and verified result schema.

• Plain query: a column that references a field outside an aggregate makes the result one row per input row. Aggregates, which run over all rows, are then repeated on every row. If no column references a field outside an aggregate, the result is a single row, even when where leaves no rows. This includes a select of only literals and parameters.
• Grouped query: one row per group.

orderBy

Each item is { "expression": …, "direction": "asc" | "desc" }, with direction defaulting to asc. The expression is in projection context for a plain query and group context for a grouped one, and may be of any type. Each type orders as defined under Ordering and equality. Items apply in order, each breaking the ties of the previous one. To sort by a computed column, repeat its expression.

distinct and pagination

distinct: true removes duplicate result rows. pagination is { "skip": ≥ 0, "take": ≥ 1 }. Each of skip and take is either a number or a non-null integer parameter, e.g. { "skip": { "param_name": "offset", "type": { "name": "integer" } }, "take": 20 }, so the page is chosen at execution time. The schema checks the range of a number; binding a parameter outside it (a negative skip, a take below 1) is an execution error. Aggregates over all rows are computed before distinct and pagination.

Subqueries

The main query may declare named helper queries:

{
  "subqueries": [
    { "name": "user_totals", "query": { "from": { "entity": "orders" }, "groupBy": [ … ], "select": [ … ] } },
    { "name": "big_spenders", "query": { "from": { "subquery": "user_totals" }, "where": …, "select": [ … ] } }
  ],
  "from": { "entity": "users" },
  "joins": [ { "type": "inner", "subquery": "big_spenders", "on": … } ],
  "select": [ … ]
}

• A subquery is read through from / join { "subquery": <name> }. Its columns are fields: { "source": <name or alias>, "field": <column alias>, "type": … }.
• in accepts one column of a subquery as its list, which gives a semi-join or, with not, an anti-join: { "subquery": <name>, "field": <column alias>, "type": … }.
• The list is flat: subqueries cannot declare subqueries, and there is no recursion.

---

Operators

The tables use T for any type and T? for its nullable form.

Boolean and comparison

Operator	Shape	Result
and, or	{ conditions: [boolean, …] } (at least 1)	boolean
not	{ condition: boolean }	boolean
equal, notEqual	{ left: T?, right: T? }, T any type	boolean
greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual	{ left: T?, right: T? }, where T is integer / decimal, string, date, time or datetime	boolean
in	{ value: T?, list: <list of T> }	boolean

Arithmetic

Operator	Shape	Result
add, subtract, multiply	{ values: [number, …] } (at least 2, left to right)	integer or decimal
divide	{ values: [number, …] } (at least 2, left to right)	decimal
integerDivide, modulo	{ left: integer, right: integer }	integer
floor, ceiling	{ value: decimal }	integer
round	{ value: decimal } / { value: decimal, digits: integer }; digits is non-null and may be negative or computed	integer / decimal

Strings, dates and times

Operator	Shape	Result
concat	{ values: [string, …] } (at least 2)	string
dateAddDays	{ left: date, right: integer }	date
dateDiffDays	{ left: date, right: date }	integer (left − right)
timeAddSeconds	{ left: time, right: decimal }	time
timeDiffSeconds	{ left: time, right: time }	decimal
datetimeAddSeconds	{ left: datetime, right: decimal }	datetime
datetimeDiffSeconds	{ left: datetime, right: datetime }	decimal

Every number operand above also accepts an integer. Larger units are composed, for example datetimeAddSeconds(dt, multiply(hours, 3600)). timeAddSeconds wraps around midnight; see Dates and times.

Conditional and null handling

Operator	Shape	Result
if	{ condition: boolean, then: T, else: T }	T (T? if either branch is nullable)
coalesce	{ values: [T?, …] } (at least 2)	T if any operand is non-null, otherwise T?

Aggregates

{ "operator": "sum", "over"?: "group" | "all", "selector": <row expression>, "predicate"?: <row boolean> }

Operator	Selector	Result
count	none	integer
sum	integer / decimal	type of the selector; 0 for no rows, never null
average	decimal, date, time, datetime	decimal for numbers, otherwise the selector type
min, max	integer, decimal, string, date, time, datetime	type of the selector
any, all	none; predicate required	boolean. Over no rows, any is false and all is true

• over is optional. Without it an aggregate runs over the current group in a grouped query and over every row after where in a plain one, as in SQL. Write over: "all" in a grouped query for a total over every row, e.g. a group's share of all revenue; over: "group" is accepted in grouped queries but never needed.
• predicate filters the rows the aggregate sees: count with a predicate counts the matching rows. count without one counts every row.
• null values from the selector are skipped.
• average, min and max are non-null only over a group (no over or over: "group" in a grouped query), with no predicate and a non-null selector, because a group always has at least one row. Otherwise they are nullable: in a plain query there may be no rows at all.

---

Semantics

This section fixes what an interpreter computes, so that two conforming interpreters return the same rows for the same query, data and parameters. An error fails the whole query: no rows are returned.

Evaluation

• Sources and joins. from yields the rows of its entity or subquery. Each join combines the rows so far with the joined source: inner keeps the pairs for which on is true; left also keeps every row so far that matched nothing, with null for the joined source's fields; right also keeps every joined row that matched nothing, with null for the fields of all earlier sources; full does both. on with the literal true is a cross join.
• where keeps the rows whose condition is true.
• groupBy partitions the rows by their keys, compared with equal: all null keys form one group, and 1 and 1.0 are the same decimal key. A group always has at least one row, so a grouped query over no rows returns no rows. having keeps the groups whose condition is true.
• select yields one row per group in a grouped query. In a plain query it yields one row per row when a column reads a field outside an aggregate, and exactly one row otherwise.
• distinct removes rows equal to an earlier row in every column, compared with equal.
• orderBy sorts by its keys in turn, by the order of each type. Rows equal on every key come in an unspecified order. With distinct, a key that is not determined by the selected columns leaves the order of the distinct rows unspecified.
• pagination skips skip rows, then returns at most take.
• Subqueries behave as if each ran once, before the main query. A subquery's result is an unordered collection: its orderBy only decides which rows its pagination keeps.
• Laziness. An operation raises an error only if it is evaluated, and evaluation is lazy where it matters: if evaluates only the branch it takes, coalesce stops at its first non-null operand, and stops at the first false condition and or at the first true. So if(equal(n, 0), 0, divide(x, n)) never divides by zero.

Execution errors

A query fails at execution with an error when it:

• produces an integer outside the 64-bit range, or a decimal beyond the interpreter's range;
• divides by zero in divide, integerDivide or modulo;
• produces a date or datetime outside the years 0000–9999;
• is run with a missing parameter, a parameter of the wrong type, null for a non-null parameter, a datetime parameter without an offset, or a pagination parameter below its minimum (skip < 0, take < 1).

Integer and decimal arithmetic

• integer is a signed 64-bit integer, from −2⁶³ to 2⁶³ − 1. Any integer result outside that range is an error, including sum, round, floor and ceiling.
• decimal is an exact decimal number, never binary floating point: 0.1 + 0.2 equals 0.3. Literals and parameters are read exactly. An interpreter keeps at least 28 significant digits; a result that needs more is rounded half away from zero, so interpreters agree on every result that fits in 28 digits.
• Mixed operands. An integer operand of a decimal operation is converted exactly. Comparisons and equal between integer and decimal compare numeric values: 2 equals 2.0.
• divide computes the exact quotient, rounded half away from zero to the decimal precision: divide(1, 3) is 0.3333333333333333333333333333 with 28 digits.
• integerDivide truncates toward zero, and modulo takes the sign of its left operand, so left = right × integerDivide(left, right) + modulo(left, right): integerDivide(-7, 2) is -3 and modulo(-7, 2) is -1.
• floor and ceiling round toward −∞ and +∞. round rounds half away from zero: round(2.5) is 3 and round(-2.5) is -3. With digits, it rounds to that many decimal places, and a negative digits rounds to tens, hundreds and so on: round(1234.5, -2) is 1200.

Strings

A string is a sequence of Unicode code points. equal compares code point by code point: there is no case folding, Unicode normalisation or trimming, so "a" and "A" differ, and so do "a" and "a ". Ordering, used by comparisons, orderBy, min and max, is lexicographic by code point, and a string sorts before every longer string it is a prefix of. concat joins its operands unchanged.

Ordering and equality

Type	Equality	Order
integer, decimal	numeric value	numeric
string	code points	lexicographic by code point
boolean	value	false before true
date	calendar day	chronological
time	time of day	chronological from 00:00:00
datetime	instant	chronological by instant
uuid	value; letter case in a literal does not matter	lexicographic over the 32 hexadecimal digits in lower case, which is the order of the 16 bytes read as unsigned big-endian

null equals null and sorts first in ascending order (see Null semantics). The uuid order is not the one SQL Server uses for uniqueidentifier; an interpreter on such a database must still sort by this rule.

Dates and times

• date follows the proleptic Gregorian calendar. dateAddDays adds whole calendar days, and dateDiffDays is left − right in days.
• time is a time of day with nanosecond precision, from 00:00:00 up to but excluding 24:00:00. timeAddSeconds wraps around midnight: 23:00:00 plus 7200 seconds is 01:00:00, and negative seconds go backwards. timeDiffSeconds is left − right in seconds without wrapping, so it is negative when left is earlier.
• datetime is an instant with nanosecond precision; every day has 86400 seconds. datetimeAddSeconds and datetimeDiffSeconds work on the instant. The offset is notation only, and datetime results are returned in UTC, written with Z.
• A number of seconds with more than 9 fractional digits is rounded to nanoseconds, half away from zero.

Aggregate evaluation

An aggregate sees the rows of its group, or every row left by where with over: "all", and of those only the rows for which its predicate is true.

Operator	Value	With no rows, or only null values
count	number of rows	0
sum	exact sum of the non-null values; an integer overflow is an error	0
average	mean of the non-null values. Numbers: sum divided as by divide. date: mean day, rounded half away from zero to a whole day. time: mean of the times as seconds since midnight, without wrapping. datetime: mean instant, returned in UTC. time and datetime round to nanoseconds	null
min, max	least and greatest non-null value by the order of its type	null
any	true when some row satisfies the predicate	false
all	true when every row satisfies the predicate	true

Parameters

• A query is run with a value for every parameter it uses. A value is written like a literal of the declared type: a JSON number for integer and decimal, the literal formats for date, time, datetime and uuid. A list parameter is a JSON array of non-null values of the element type, possibly empty.
• A nullable parameter may be bound to null; a non-null one may not. Bound values that the query does not use are ignored.
• One parameter name has one type in the whole document, subqueries included.

Names and sources

• Each source of a query, the main query or a subquery, has a name: its alias, or its entity or subquery name when there is no alias. These names are unique within the query, and fields are referenced through them only: a source with an alias cannot be referenced by its entity or subquery name.
• Subquery names are unique in the document. A subquery may read entities and the subqueries declared before it; the main query may read every subquery.
• A subquery's columns are its select aliases, with their declared types.
• Names compare exactly, code point by code point.

---

Validation

The schema checks everything about a query's shape and types. The interpreter checks what depends on the catalog, on parameter values or on names:

Rule	Checked by
Shape of every node, literal formats, unknown keys	schema
Operand and result types, nullability, implicit conversions	schema
Where fields, keys and aggregates may appear (contexts)	schema
Each select column's and groupBy key's expression against its declared type	schema
Only the main query declares subqueries; a source is an entity or a subquery	schema
Entities, fields and parameters exist and have the declared types; a parameter used in several places has one type	interpreter
A datetime parameter is bound with an offset	interpreter
A pagination parameter is bound to skip ≥ 0 / take ≥ 1	interpreter
Each field reference declares exactly the nullability the field has at that point, outer joins included	interpreter
A group key reference points to an existing key and repeats its declared type	interpreter
Subquery names are unique; a subquery reads only from earlier ones	interpreter
Source names are unique within a query, and an aliased source is referenced by its alias	interpreter
Referenced subquery columns exist with the declared types	interpreter
Column aliases are unique within a select	interpreter

The interpreter's part needs no type inference. Besides lookups, it tracks which sources are on the optional side of an outer join at each point of the query. Every expression that is referenced from elsewhere declares its type, and the schema has already checked that declaration: select columns (referenced as subquery columns) and groupBy keys (referenced as { "key": i }). The interpreter only compares names and declared types against the catalog, the groupBy list and the subquery headers.

Not specified yet

• how results are encoded for transport, e.g. decimal values as JSON numbers or strings;
• string functions beyond concat, conversions between types, date parts;
• correlated subqueries and set operations.

---

Tooling

Path	Purpose
tools/generate_schema.py	Generates PureQL-Specification.json. Edit this file, never the schema

python3 tools/generate_schema.py   # after changing the generator
npx --yes @prantlf/jsonlint@17.0.1 --check --indent 2 --trailing-newline --no-duplicate-keys PureQL-Specification.json
npx --yes @prantlf/jsonlint@17.0.1 --check --continue --indent 2 --trailing-newline --no-duplicate-keys "samples/*.json"
npx --yes ajv-cli@5.0.0 test --spec=draft2020 --strict=false -s PureQL-Specification.json -d "samples/*.json" -d "tests/valid/*.jsonc" --valid
npx --yes ajv-cli@5.0.0 test --spec=draft2020 --strict=false -s PureQL-Specification.json -d "tests/invalid/*.jsonc" --invalid

The generator needs Python; validation needs only Node.js. The schema has a root version keyword, which JSON Schema does not define, so a validator in strict mode must allow it (--strict=false for ajv). On every pull request CI also checks that PureQL-Specification.json is formatted exactly as the generator writes it, with 2-space indent, a final newline and no duplicate keys, and that every sample is formatted the same way, and fails with a diff otherwise. jsonlint --in-place with the same options formats a new sample. It runs the two ajv commands on every pull request and before every release: samples/ and tests/valid/ must pass, tests/invalid/ must fail. tests/valid/001_deep_nesting.jsonc nests equal(if(…)) 30 levels deep and guards against exponential validation time.

Tests are written by hand. Each invalid test is a valid base query with exactly one thing broken, written as JSONC: a // comment describing what is broken, then the bare query. A new test takes the next free number.

---

Samples

The samples/ directory holds valid queries ordered from simple to complex, in the e-commerce domain.

File	Description
01_select_single_field.json	Select one field from one entity
02_from_alias.json	from alias: fields are referenced through the alias
03_literal_columns.json	A literal of every type as a column next to a field
04_parameters.json	Parameters in where and as a column
05_where_equal.json	Filter on equality with a string literal
06_where_boolean_field.json	A boolean field is a condition on its own
07_where_not_and_not_equal.json	notEqual and not
08_where_nested_and_or.json	Nested and / or
09_where_decimal_ranges.json	Range comparisons on decimal against literals, a parameter and another field
10_where_other_comparisons.json	Range comparisons on string, date and time
11_in_list.json	Membership in a list parameter and a list literal
12_order_by_multiple_keys.json	orderBy with several keys and directions
13_distinct.json	distinct over a single column
14_filter_order_page.json	Row filter with a parameter, ordering and pagination
15_integer_arithmetic.json	add / subtract / multiply over integers stay integer
16_decimal_widening_and_divide.json	integer widens to decimal; divide is always decimal
17_rounding_and_integer_division.json	floor / ceiling / round, round with digits, integerDivide and modulo
18_concat.json	String concatenation
19_if_column.json	if as a column; an integer branch widens to decimal
20_order_by_computed.json	orderBy on a computed per-row expression
21_date_math.json	dateAddDays / dateDiffDays as columns and in where
22_time_datetime.json	time / datetime literals, math and comparisons
23_datetime_offsets.json	datetime literals with a non-UTC offset and with fractional seconds
24_nullable_columns.json	Nullable fields selected into nullable columns
25_typed_null.json	Typed null literals, and integer? widened to decimal?
26_coalesce.json	coalesce: non-null once one operand is non-null, nullable otherwise
27_null_checks.json	Testing for null with equal / notEqual and a typed null
28_nullable_boolean_conditions.json	A nullable boolean becomes a condition via equal(…, true) or coalesce(…, false)
29_lifted_operators.json	Lifted operators: a nullable operand makes the column nullable
30_inner_join.json	Inner join on a key
31_left_join_nullable.json	Left join: nullable fields, typed null, lifted arithmetic, coalesce
32_self_join.json	Self-join through an alias on the join
33_join_on_nullable_key.json	Join on a key nullable on both sides: notEqual(…, null) keeps two nulls from matching
34_right_join.json	Right join: the left side becomes nullable
35_full_join.json	Full join: both sides nullable, coalesced into one key
36_multiple_joins.json	Several joins of different kinds
37_outer_join_chain.json	Chained left joins: a non-null field of the first optional side is nullable in the next on
38_aggregates_over_all_rows.json	Aggregates over all rows: count / sum non-null, min / max / average nullable
39_aggregate_with_predicate_broadcast.json	Filtered aggregates over all rows next to row columns
40_share_of_total.json	A row value divided by an aggregate over all rows
41_group_by_count.json	Group by one field and count
42_group_by_multiple_keys.json	Group by two fields
43_computed_group_key.json	Group by a computed key, select it and order groups by it
44_having.json	having on an aggregate
45_having_boolean_logic.json	having combining aggregates with and / or / not and a parameter
46_conditional_aggregates.json	count / sum with a predicate, sum over if, any / all
47_group_key_usage.json	Plain, nullable and computed group keys in select, having and orderBy
48_nullable_aggregates_and_keys.json	Nullable group key; non-null and nullable aggregates
49_date_math_everywhere.json	Date math per row and over aggregates with the same operators
50_integer_decimal.json	integer / decimal rules inside a grouped query
51_grouped_revenue.json	Revenue per user with a share of all rows, having and orderBy
52_subquery_from.json	Main query reading from one subquery
53_in_subquery.json	Semi-join: in over one column of a subquery
54_subquery_chain.json	A grouped subquery, a second one reading from it, the main query joining the second
55_left_join_subquery.json	Left join to a subquery: its columns become nullable
56_subquery_full_pipeline.json	Three subqueries feeding a joined, filtered, ordered, paged main query