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. Every expression, operators included, declares its type, so a query carries the type of each node and an interpreter reads it rather than computing it. 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 `kind` and an `on` condition. Omit rather than leave empty
`where`	no	Row filter, before grouping
`groupBy`	no	Group keys, each `{ alias, expression }` with any row expression
`having`	no	Group filter; only with `groupBy`
`select`	yes, or `aggregate`	Result columns, each `{ alias, expression }`; a column has the type its expression declares
`aggregate`	instead of `select`	Columns of a total query, which returns exactly one row. See `aggregate`
`orderBy`	no	Sort keys, each `{ expression, direction }`. Omit rather than leave empty
`distinctBy`	no	`select` aliases: of the rows equal on those columns, keep the first in `orderBy` order. Listing every alias removes duplicate rows
`pagination`	no	`{ skip, take }`, each a number or an `integer` parameter. Requires `orderBy`

There are three kinds of query, told apart by their shape:

a plain query has select and returns one row per row of its sources;
a grouped query has groupBy and select, and returns one row per group;
a total query has aggregate instead of select, and returns exactly one row, even when there are no rows.

Each has its own rules for what its columns may read (see Contexts).

Type System

Types

Type	Literal value	Example
`integer`	JSON integer from −2⁶³ to 2⁶³ − 1: a number with no fractional part, so `5.0` counts	`42`
`decimal`	a JSON string holding the number in one spelling: no exponent, no leading zeros, no trailing zeros in the fraction, no `-0`; at most 28 significant digits and 28 decimal places	`"19.99"`
`string`	JSON string	`"active"`
`boolean`	`true` / `false`	`true`
`date`	`YYYY-MM-DD`, a real calendar date from `0001-01-01` to `9999-12-31` (February 29 only in leap years)	`"2024-01-31"`
`time`	`hh:mm:ss[.fraction]`: hours `00`–`23`, seconds `00`–`59`, 1 to 6 fractional digits (microseconds), the last not `0`	`"18:30:00.125"`
`datetime`	a `date`, `T`, a `time`, then `Z`: always in UTC (see Temporal types)	`"2024-01-31T15:30:00Z"`
`uuid`	8-4-4-4-12 hexadecimal digits in lower 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

A non-null type is written without nullable; "nullable": false is rejected, so each type has one spelling and declared types compare as written.

The schema rejects an integer literal outside the 64-bit range, up to the precision of JSON numbers. Its bounds are written as ±9223372036854776000, the way JavaScript prints ±2⁶³: a validator that reads numbers as binary doubles, as JavaScript does, applies exactly ±2⁶³, and one that reads them exactly lets through at most 192 more values on each side. The interpreter rejects the few values the schema lets past the real bounds. A client that reads numbers as doubles also cannot send integers beyond ±2⁵³ exactly.

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, group or total, fixed by its position), never inferred from its surroundings. The context decides where fields, group keys and aggregates may appear, whether over may be written, and the rows an aggregate runs over, so whether average, min and max can be null: always in a total query, which returns its row even over no rows. With implicit conversions this is the narrowest type: an integer expression is also a valid decimal. Every expression declares this type; see Declared types.

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.

Declared types

Every expression has a type: a field, parameter, literal or group key declares the type of the value it reads, and an operator declares the type it yields:

{ "operator": "subtract", "type": { "name": "decimal", "nullable": true },
  "values": [ { "source": "orders", "field": "total",    "type": { "name": "decimal" } },
              { "source": "orders", "field": "discount", "type": { "name": "decimal", "nullable": true } } ] }

Exact. An operator declares exactly the type it yields from the types its operands declare, as given under Operators and Null semantics, never a wider one. add of two integer operands is declared integer, not decimal; a lifted operator is declared nullable exactly when one of its operands is; coalesce is non-null exactly when one of its operands is. So an expression has one valid declared type.
One spelling. Redundant spellings are rejected as well, where the form of the query alone shows them: "nullable": false; and / or with a single condition; defaults written out (over stating the default, "direction": "asc"); a literal written in a second way (decimal "1.50", time "18:30:00.500", a datetime with an offset other than Z, a uuid in upper case); a repeated value in a list literal; and a coalesce operand that is never reached, after another non-null one (that the non-null operand comes last is beyond a schema). Equivalences between expressions are out of scope: and(a, and(b, c)) and and(a, b, c), not(not(x)) and x, not(equal(a, b)) and notEqual(a, b), if(true, a, b) and a are all accepted, as any language accepts them. Nor can the schema see how a JSON number is written: 5 and 5.0 are the same integer.
Implicit conversions apply at the position, not in the declaration. An integer operator stands wherever a decimal is expected, and a non-null one wherever a nullable one is, exactly as a field would.
Checked by the schema. A position reads only the type its operand declares, and each operator checks its declared type against its operands' declared types. Each node is therefore validated once, and validation time grows linearly with the size of the query, whatever its depth.
Read by the interpreter. The type of any node is the one it declares, so building a typed expression tree needs no type computation.
Columns and keys. A select column and a groupBy key have the type their expression declares; they carry no type of their own. So the result schema of a query is read from select[i].expression.type, and a key reference { "key": <alias>, "type": … } repeats the type of that key's expression.

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:
- It is always written in UTC, ending in Z, with T and Z in upper case. An instant has one spelling: 2024-01-01T03:00:00+03:00 is written 2024-01-01T00:00:00Z, and any other offset, +00:00 included, is rejected.
- Comparison, equal, *DiffSeconds, min / max, orderBy, groupBy and distinctBy all work on the instant, and datetime results are returned in UTC, written with Z.
- A datetime parameter is bound in UTC too. 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": "since", "type": { "name": "datetime" } }`	Bound at execution time; may be nullable
List	`{ "type": { "name": "stringList" }, "value": ["a", "b"] }`, a list parameter `{ "param": …, "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": "user_id", "type": { "name": "uuid" } }`	The `alias` of a `groupBy` key, repeating the type the key's expression declares; only in grouped `select`, `having` and `orderBy`
Operator	`{ "operator": "add", "type": { "name": "integer" }, "values": [ … ] }`	Declares the type it yields, exactly. See Declared types and 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. Operators declare their result type the same way, and the schema checks it against their operands.

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, without `over`
total	`aggregate` of a total query	no	no	over all rows, without `over`
group	`select` / `having` / `orderBy` of a grouped query	no	yes	over the group, or over all rows with `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.
A total query reads fields only inside aggregates, and has no orderBy, distinctBy, pagination or having.

Query Clauses

A query runs in this order: from and joins, where, groupBy, having, select (or aggregate), orderBy, distinctBy, 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": [
  { "kind": "left", "entity": "coupons",
    "on": { "operator": "equal", "type": { "name": "boolean" },
            "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 kinds (kind): 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 034_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 038_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",
  "expression": { "operator": "dateDiffDays", "type": { "name": "integer", "nullable": true },
                  "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. A key has the type its expression declares. 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. Every key has an alias, unique among the keys of the query.
Referencing. A key is referenced by its alias as { "key": "days_to_ship", "type": … }, and the reference repeats the type of the key's expression exactly. Matching the two is therefore a plain lookup, and reordering the keys changes nothing.
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 has an alias and an expression:

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

A column has the type its expression declares, here decimal, which the schema has checked like every other declaration. So every query has a declared and verified result schema: an alias and a type per column. A column is never wider than its expression: a sum of integer values is an integer column.

Plain query: one row per row left by where, whatever the columns read. Aggregates, which run over all rows, are repeated on every row, and a select of only literals returns one row per row too. For a single row, use aggregate.
Grouped query: one row per group.

`aggregate`

A total query has aggregate instead of select, with the same { alias, expression } columns:

{ "from": { "entity": "orders" },
  "aggregate": [
    { "alias": "orders",  "expression": { "operator": "count", "type": { "name": "integer" } } },
    { "alias": "largest", "expression": { "operator": "max", "type": { "name": "decimal", "nullable": true },
                                          "selector": { "source": "orders", "field": "total", "type": { "name": "decimal" } } } } ] }

It returns exactly one row, even when where leaves no rows. Its columns are in total context: they read fields only inside aggregates, and every aggregate runs over all rows, so over is not written. With no rows, count and sum are 0 and min, max and average are null, so these three are always nullable here. A total query has no orderBy, distinctBy or pagination, which would act on its one row, and no having: filter with where. As a subquery it gives a one-row source, e.g. joined on the literal true next to every row.

`orderBy`

Each item is { "expression": … } for ascending order or { "expression": …, "direction": "desc" } for descending; "asc" is never written. 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.

`distinctBy` and `pagination`

distinctBy lists select aliases, each once. Of the rows equal on those columns, compared with equal, it keeps the first in orderBy order and drops the others:

"orderBy": [ { "expression": { "source": "orders", "field": "ordered_at", "type": { "name": "datetime" } }, "direction": "desc" },
             { "expression": { "source": "orders", "field": "id", "type": { "name": "uuid" } } } ],
"distinctBy": [ "user_id" ]

keeps each user's latest order (014_distinct_by_first_row.json). Listing every alias removes duplicate rows, as SQL DISTINCT does; rows equal in every column are interchangeable, so no order is needed then. Otherwise, which row is kept is defined only as far as orderBy tells the rows apart, as with pagination below. Without distinctBy duplicate rows are kept.

pagination is { "skip": ≥ 0, "take": ≥ 1 } and requires orderBy, since without an order two interpreters may return different pages. The order should also be total: rows equal on every orderBy key may fall on either side of a page boundary, so end the keys with one that identifies a row, such as an id. The schema cannot check this, as it does not know which columns are unique. Each of skip and take is either a number or a non-null integer parameter, e.g. { "skip": { "param": "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 distinctBy 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": [ { "kind": "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. Every operator node also has a type: the result in the last column, declared exactly (see Declared types). Unless stated otherwise, the result is nullable exactly when an operand is: arithmetic, concat, date and time math, round, floor and ceiling are lifted (see Null semantics).

Boolean and comparison

Operator	Shape	Result
`and`, `or`	`{ conditions: [boolean, …] }` (at least 2)	`boolean`
`not`	`{ condition: boolean }`	`boolean`
`equal`, `notEqual`	`{ left: T?, right: T? }`, `T` any type	`boolean`
`greaterThan`, `lessThan`, `greaterThanOrEqual`, `lessThanOrEqual`	`{ left: T?, right: T? }`, `T` any type, compared by its order	`boolean`
`in`	`{ operand: T?, list: <list of T> }`	`boolean`

Arithmetic

Operator	Shape	Result
`add`, `subtract`, `multiply`	`{ values: [number, …] }` (at least 2, left to right)	`integer` when every operand is `integer`, otherwise `decimal`
`divide`	`{ values: [number, …] }` (at least 2, left to right)	`decimal`
`integerDivide`, `modulo`	`{ left: integer, right: integer }`	`integer`
`floor`, `ceiling`	`{ operand: decimal }`	`integer`
`round`	`{ operand: decimal }` / `{ operand: 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; `decimal` if either branch is)
`coalesce`	`{ values: [T?, …] }` (at least 2)	`T` if any operand is non-null, otherwise `T?`; `decimal` if any operand is

Aggregates

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

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

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. That is the only use of over: it is rejected in a plain query, and "group" is never written.
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 in a plain or grouped query when they have no predicate and a non-null selector: a result row exists only when a row of the sources does, so every aggregate, over the group or over all rows, sees at least one. With a predicate or a nullable selector they are nullable, and in a total query they always are, since it returns its row even over no rows.

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 row in a plain query and one row per group in a grouped query. aggregate yields exactly one row.
orderBy sorts by its keys in turn, by the order of each type. Rows equal on every key come in an unspecified order.
distinctBy then walks the sorted rows and drops each row equal, on the listed columns compared with equal, to a row before it. The rows kept stay in orderBy order.
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 and distinctBy keep, so a subquery has orderBy only together with one of them, and pagination only with orderBy.
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 of magnitude 10²⁸ or more;
divides by zero in divide, integerDivide or modulo;
produces a date or datetime outside the years 0001–9999;
is run with a missing parameter, a parameter of the wrong type, null for a non-null parameter, a parameter value not written like a literal of its type (a decimal outside its range, a datetime not in UTC, and so on), 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. Every value is m × 10⁻ˢ with an integer |m| < 10²⁸ and 0 ≤ s ≤ 28: at most 28 significant digits and 28 decimal places, which System.Decimal and every other decimal type of 28 digits or more holds exactly. A literal outside this range is rejected by the schema and a parameter outside it is an execution error, so literals and parameters are read exactly. A result that needs more digits is rounded half away from zero to the most decimal places that fit, so every interpreter computes the same result; a result of magnitude 10²⁸ or more is an error.
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

Every type is ordered, and the same order serves comparisons, min / max and orderBy.

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	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, from 0001-01-01 to 9999-12-31: year 0000 is excluded, as System.DateTime, Python, PostgreSQL and SQL Server cannot store it. dateAddDays adds whole calendar days, and dateDiffDays is left − right in days.
time is a time of day with microsecond 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 microsecond precision; every day has 86400 seconds. datetimeAddSeconds and datetimeDiffSeconds work on the instant, and datetime results are returned in UTC, written with Z.
A number of seconds with more than 6 fractional digits is rounded to microseconds, half away from zero. Microseconds are the finest precision that PostgreSQL, MySQL, SQL Server, Python, .NET and Java all hold exactly.

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 microseconds	`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 exactly like a literal of the declared type, in the same one spelling: a JSON number for integer, the literal formats for decimal (a string within its range), date, time, datetime (in UTC) and uuid (in lower case). A list parameter is a JSON array of non-null values of the element type, possibly empty, and may repeat a value.
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 (or aggregate) aliases, each with the type its expression declares.
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
Each operator declares exactly the type it yields from its operands	schema
Where fields, keys and aggregates may appear (contexts)	schema
Only the main query declares subqueries; a source is an entity or a subquery	schema
`pagination` has an `orderBy`; a subquery has `orderBy` only with `pagination` or `distinctBy`; `distinctBy` lists each alias once	schema
One spelling: no `"nullable": false`, `and` / `or` with two conditions or more, no defaults written out (`over`, `direction`), one spelling of each literal value, no repeated value in a list literal, at most one non-null `coalesce` operand; `integer` literals within 64 bits, `decimal` literals within 28 digits	schema
Entities, fields and parameters exist and have the declared types; a parameter used in several places has one type	interpreter
A parameter value is written like a literal of its type: one spelling, a `decimal` within its range, a `datetime` in UTC	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
`groupBy` aliases are unique; a group key reference names one of them and repeats the type of its expression	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
A field references a source introduced at that point: a join's `on` sees `from`, the earlier joins and the joined source; later clauses see every source	interpreter
A subquery references only its own sources, never one of the query that reads it (a correlated reference is an error)	interpreter
Referenced subquery columns exist with the declared types	interpreter
Column aliases are unique within a `select` (and within an `aggregate`)	interpreter
Each `distinctBy` entry is an alias of the query's `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 declares its type, and the schema has already checked every declaration that does not depend on the catalog: each operator against its operands. A select column (referenced as a subquery column) and a groupBy key (referenced as { "key": <alias> }) have the type of their expression. The interpreter only compares names and the declared types of fields, parameters and key references against the catalog, the groupBy list and the subquery headers, and reads the type of every other node as declared.

Not specified yet

how results are encoded for transport; literals and parameters write a decimal as a string, results may follow;
string functions beyond concat, conversions between types, date parts;
set operations. Correlated subqueries are not part of the language: a reference from a subquery to a source of the query that reads it is an error.

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/valid/*.json"
npx --yes ajv-cli@5.0.0 test --spec=draft2020 --strict=false -s PureQL-Specification.json -d "samples/valid/*.json" -d "samples/valid/*.jsonc" --valid
npx --yes ajv-cli@5.0.0 test --spec=draft2020 --strict=false -s PureQL-Specification.json -d "samples/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: everything in samples/valid/ must pass, everything in samples/invalid/ must fail. samples/valid/058_deep_nesting.jsonc nests equal(if(…)) 30 levels deep and guards against exponential validation time; samples/valid/067_deep_operand_chains.jsonc and samples/invalid/0330_deep_malformed_operand_type.jsonc do the same for other operators and for a malformed node.

Reading the schema. Every definition is short and named after what it checks. expr@row is any expression in row context (likewise @projection and @group); it sends an operator node to operator@row, which sends each operator to its own definition, and a leaf to leaf@row. An operator definition lists its signatures by declared type: in add@row, a node declaring decimal? is checked by add.decimal.nullable@row. A position that expects a type is decimal.nullable@row: an expression of row context whose declared type accepts.decimal.nullable. declares.* and accepts.* read only the type a node declares.

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/valid/087–090 use every operator in every signature, and every aggregate, once in each context (a groupBy key, a plain select, a grouped select, a total query). The invalid tests after 0411 break one thing at a time in each context: an unknown property, a missing operand, an operand of the wrong type or with a malformed value, a field, group key or aggregate where the context has none, a declared type that is not the exact one, in lists of every kind, and the clauses of each kind of query. They were chosen by deleting or loosening each constraint of the schema in turn and keeping the tests that notice it.

Samples

The samples/valid/ directory holds the samples below (.json), valid queries ordered from simple to complex, in the e-commerce domain. Next to them are valid tests (.jsonc) for edge cases, and samples/invalid/ holds the queries that must be rejected.

File	Description
`001_select_single_field.json`	Select one field from one entity
`002_from_alias.json`	`from` alias: fields are referenced through the alias
`003_literal_columns.json`	A literal of every type as a column next to a field
`004_parameters.json`	Parameters in `where` and as a column
`005_where_equal.json`	Filter on equality with a string literal
`006_where_boolean_field.json`	A boolean field is a condition on its own
`007_where_not_and_not_equal.json`	`notEqual` and `not`
`008_where_nested_and_or.json`	Nested `and` / `or`
`009_where_decimal_ranges.json`	Range comparisons on `decimal` against literals, a parameter and another field
`010_where_other_comparisons.json`	Range comparisons on `string`, `date` and `time`
`011_in_list.json`	Membership in a list parameter and a list literal
`012_order_by_multiple_keys.json`	`orderBy` with several keys and directions
`013_distinct.json`	`distinctBy` over every column: duplicate rows removed
`014_distinct_by_first_row.json`	`distinctBy` on one column: each user's latest order, the first in `orderBy` order
`015_filter_order_page.json`	Row filter with a parameter, ordering and pagination
`016_integer_arithmetic.json`	`add` / `subtract` / `multiply` over integers stay `integer`
`017_decimal_widening_and_divide.json`	`integer` widens to `decimal`; `divide` is always `decimal`
`018_rounding_and_integer_division.json`	`floor` / `ceiling` / `round`, `round` with digits, `integerDivide` and `modulo`
`019_concat.json`	String concatenation
`020_if_column.json`	`if` as a column; an `integer` branch widens to `decimal`
`021_order_by_computed.json`	`orderBy` on a computed per-row expression
`022_date_math.json`	`dateAddDays` / `dateDiffDays` as columns and in `where`
`023_time_datetime.json`	`time` / `datetime` literals, math and comparisons
`024_datetime_utc.json`	`datetime` literals, always in UTC, with fractional seconds
`025_nullable_columns.json`	Nullable fields selected into nullable columns
`026_typed_null.json`	Typed null literals, and `integer?` widened to `decimal?`
`027_coalesce.json`	`coalesce`: non-null once one operand is non-null, nullable otherwise
`028_null_checks.json`	Testing for null with `equal` / `notEqual` and a typed null
`029_nullable_boolean_conditions.json`	A nullable boolean becomes a condition via `equal(…, true)` or `coalesce(…, false)`
`030_lifted_operators.json`	Lifted operators: a nullable operand makes the column nullable
`031_inner_join.json`	Inner join on a key
`032_left_join_nullable.json`	Left join: nullable fields, typed null, lifted arithmetic, `coalesce`
`033_self_join.json`	Self-join through an alias on the join
`034_join_on_nullable_key.json`	Join on a key nullable on both sides: `notEqual(…, null)` keeps two nulls from matching
`035_right_join.json`	Right join: the left side becomes nullable
`036_full_join.json`	Full join: both sides nullable, coalesced into one key
`037_multiple_joins.json`	Several joins of different kinds
`038_outer_join_chain.json`	Chained left joins: a non-null field of the first optional side is nullable in the next `on`
`039_aggregates_over_all_rows.json`	A total query (`aggregate`): one row; `count` / `sum` non-null, `min` / `max` / `average` nullable
`040_aggregate_with_predicate_broadcast.json`	Filtered aggregates over all rows next to row columns
`041_share_of_total.json`	A row value divided by an aggregate over all rows
`042_group_by_count.json`	Group by one field and count
`043_group_by_multiple_keys.json`	Group by two fields
`044_computed_group_key.json`	Group by a computed key, select it and order groups by it
`045_having.json`	`having` on an aggregate
`046_having_boolean_logic.json`	`having` combining aggregates with `and` / `or` / `not` and a parameter
`047_conditional_aggregates.json`	`count` / `sum` with a predicate, `sum` over `if`, `any` / `all`
`048_group_key_usage.json`	Plain, nullable and computed group keys in `select`, `having` and `orderBy`
`049_nullable_aggregates_and_keys.json`	Nullable group key; non-null and nullable aggregates
`050_date_math_everywhere.json`	Date math per row and over aggregates with the same operators
`051_integer_decimal.json`	`integer` / `decimal` rules inside a grouped query
`052_grouped_revenue.json`	Revenue per user with a share of all rows, `having` and `orderBy`
`053_subquery_from.json`	Main query reading from one subquery
`054_in_subquery.json`	Semi-join: `in` over one column of a subquery
`055_subquery_chain.json`	A grouped subquery, a second one reading from it, the main query joining the second
`056_left_join_subquery.json`	Left join to a subquery: its columns become nullable
`057_subquery_full_pipeline.json`	Three subqueries feeding a joined, filtered, ordered, paged main query

Name		Name	Last commit message	Last commit date
.claude		.claude
.github/workflows		.github/workflows
samples		samples
tools		tools
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
CLAUDE.md		CLAUDE.md
CODEOWNERS		CODEOWNERS
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
LICENSE		LICENSE
PureQL-Specification.json		PureQL-Specification.json
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation

PureQL Specification

Quick Reference

Type System

Types

Null

Implicit conversions

Declared types

Null semantics

Numbers

Temporal types

Building Blocks

Contexts

Query Clauses

from and joins

where

groupBy and group keys

having

select

aggregate

orderBy

distinctBy and pagination