SQL syntax & examples #

The /_sql frontend accepts a SQL-92-shaped SELECT subset with Pizza extension functions. This page walks it clause by clause, with runnable examples — every shape below is covered by the gateway’s end-to-end tests. See SQL & PRQL for the wire contract and the response envelope, and PRQL queries for the pipeline frontend.

Statements are case-insensitive (SELECT = select), -- starts a line comment, and the overall shape is:

SELECT …
FROM collection
[WHERE …]
[GROUP BY …]
[HAVING …]
[ORDER BY … [ASC|DESC], …]
[LIMIT n [OFFSET m]]

One collection in FROM (bare name = default namespace; JOIN, subqueries and WITH are not in the subset yet). Relations (traverse/join walks over relation fields) and geo filters stay DSL-side — demand-gated outside this subset.

Every ▶ Try it live widget on this page runs the statement in your browser — the engine compiled to WebAssembly, on the same teaching dataset as the playground (logs, sales, products, … instead of the e2e fixtures used in the curl examples). The full interactive surface — mode toggle, per-dataset example chips, $1 params box — lives in the playground.

Literals and identifiers #

_key addresses the document key. Field names are bare identifiers; unknown fields answer NULL (three-valued logic below), not an error.

WHERE — filters #

Comparison and boolean algebra, with parentheses:

SELECT _key FROM sq_nulls WHERE price > 15
SELECT _key FROM sq_nulls WHERE tag != 't0' OR price IS NULL
SELECT _key FROM sq_nulls WHERE NOT (tag = 't0' AND price > 15)

Set and range predicates:

SELECT _key FROM sq_orders WHERE tier IN ('silver', 'bronze')
SELECT _key FROM sq_nulls WHERE tag NOT IN ('t0')
SELECT _key FROM sq_nulls WHERE price BETWEEN 20 AND 50
SELECT _key FROM sq_nulls WHERE price NOT BETWEEN $1 AND $2

LIKE patterns: % matches any run of characters, _ exactly one (standard SQL wildcards; an explicit ESCAPE clause is outside the subset). NOT LIKE inverts. Patterns can bind to params:

SELECT _key FROM sq_orders WHERE name LIKE 'pha%'
SELECT _key FROM sq_nulls WHERE tag NOT LIKE 't0%'
SELECT _key FROM sq_nulls WHERE tag LIKE $1        -- params: ["t1%"]

NULL is three-valued: a missing field answers UNKNOWN to every polarity, so price > 15 and NOT (price > 15) both drop documents without a price. Test existence explicitly:

SELECT _key FROM sq_nulls WHERE price IS NULL
SELECT COUNT(*) FROM sq_nulls WHERE price IS NOT NULL

Full-text and relevance extensions #

-- full-text match, both spellings
SELECT _key FROM sq_orders WHERE note MATCH 'delivery'
SELECT _key FROM sq_nulls WHERE NOT MATCH(tag, 't0')
-- exact phrase
SELECT _key FROM sq_orders WHERE MATCH_PHRASE(note, 'rush delivery')
-- typo tolerance
SELECT _key FROM sq_orders WHERE FUZZY(tier, 'goldd', 'AUTO')
-- any raw search-DSL body as a predicate
SELECT _key FROM sq_orders WHERE QUERY('{"term": {"field": "tier", "value": "gold"}}')

Relevance travels through SCORE(): select it, or sort by it after a text or vector predicate (DESC direction is the point):

SELECT _key FROM sq_orders WHERE note MATCH 'rush' ORDER BY SCORE() DESC
SELECT _key, SCORE() FROM sq_orders WHERE note MATCH 'rush'

HIGHLIGHT(field, pre, post) returns the field with matches wrapped — it is a projection, not a filter:

SELECT HIGHLIGHT(note, '<b>', '</b>') FROM sq_orders WHERE note MATCH 'rush'

Vector search #

KNN(vector_field, query_vector, k) takes an array literal or its JSON string form; combine with ORDER BY SCORE() DESC to rank by similarity:

SELECT _key FROM sq_orders
WHERE KNN(embedding, '[1, 0, 0, 0, 0, 0, 0, 0]', 2)
ORDER BY SCORE() DESC LIMIT 2

Projection — the SELECT list #

Columns, *, expressions with arithmetic, function calls, and AS aliases. SELECT DISTINCT is not in the subset — it fails closed with a positioned error; dedupe with GROUP BY:

SELECT * FROM sq_orders WHERE tier = 'gold'
SELECT UPPER(tag) AS u FROM sq_nulls WHERE price = 10
SELECT UPPER('ab'), 1 + 2 * 3, LENGTH('héllo') FROM sq_nulls LIMIT 1

ORDER BY sorts by columns (or SCORE()), not by scalar expressions — see ORDER BY.

CASE — the searched form with column < literal ladders — works as a GROUP BY bucket key (the >/>= spellings cannot map to exact [from, to) buckets and are rejected; the simple CASE operand WHEN form is outside the subset):

SELECT CASE WHEN price < 50 THEN 'cheap'
            WHEN price < 100 THEN 'mid'
            ELSE 'high' END, COUNT(*)
FROM agg GROUP BY CASE WHEN price < 50 THEN 'cheap'
                       WHEN price < 100 THEN 'mid'
                       ELSE 'high' END

Scalar functions #

Evaluated per row on the hit lane — in the SELECT list or WHERE of a non-grouped statement. Strict typing — a non-string argument to a string function is a request error, never a reinterpreted value — and strict NULL propagation: any NULL operand answers NULL.

SELECT UPPER($1) FROM sq_nulls LIMIT 1          -- params: ["hi"]
SELECT name, LENGTH(name), YEAR(since) FROM sq_orders LIMIT 3
SELECT YEAR(tag) FROM sq_nulls                  -- error: YEAR takes a date

In a GROUPed statement the projection takes bare aggregates, group keys and literals only — SELECT UPPER(tag) … GROUP BY UPPER(tag) is outside the subset (see GROUP BY).

GROUP BY, HAVING and bucketing #

Group by plain columns (several keys are fine — order them in ORDER BY for cursor paging) or by a bucketing function; the keys are columns, DATE_TRUNC, HISTOGRAM or CASE ladders — other expressions are outside the subset:

SELECT status, COUNT(*) FROM agg GROUP BY status
SELECT tag, status, COUNT(*) FROM agg GROUP BY tag, status ORDER BY tag, status
SELECT status, COUNT(*) FROM agg GROUP BY status HAVING AVG(price) < 40 ORDER BY status

DATE_TRUNC(field, interval) buckets a date field — calendar intervals (minute hour day week month quarter year) or fixed spans like '30m', '1h', '7d':

SELECT DATE_TRUNC(created, 'day'), COUNT(*), AVG(price)
FROM agg
GROUP BY DATE_TRUNC(created, 'day')
HAVING DATE_TRUNC(created, 'day') >= '2023-11-15'
ORDER BY DATE_TRUNC(created, 'day')

HISTOGRAM(field, interval) buckets a numeric field by a fixed positive interval (integer or float):

SELECT HISTOGRAM(price, 50), COUNT(*), AVG(price)
FROM agg
GROUP BY HISTOGRAM(price, 50)
ORDER BY HISTOGRAM(price, 50)

Aggregates: COUNT(*) counts rows, COUNT(col) counts non-NULL values, COUNT(DISTINCT col) distinct values, and SUM AVG MIN MAX skip NULLs. DISTINCT also applies inside AVG/SUM:

SELECT COUNT(*), COUNT(price), SUM(price), AVG(price), MIN(price), MAX(price) FROM agg
SELECT COUNT(DISTINCT tag) FROM agg
SELECT AVG(DISTINCT price) FROM agg

ORDER BY, LIMIT, OFFSET and cursors #

ORDER BY takes columns or SCORE(), each ASC (default) or DESC — sorting by a scalar expression is outside the subset (it would order only the fetched page; project the expression and sort client-side instead). Hit-lane pages paginate with LIMIT n OFFSET m:

SELECT price FROM sq_orders ORDER BY price DESC LIMIT 2 OFFSET 1

Grouped pages paginate with a keyset cursor instead: when the plan sorts by every GROUP BY key ascending (or has no ORDER BY) with a LIMIT, the response carries a cursor object — one _kN slot per key:

{ "columns": [...], "rows": [["t1", 2], ["t2", 2]], "cursor": { "_k0": "t2" } }

Walk pages by resubmitting the SAME statement plus the cursor (and the same snapshot_id, if the walk started pinned):

POST /_sql
{ "query": "SELECT tag, COUNT(*) FROM sq GROUP BY tag ORDER BY tag LIMIT 2",
  "cursor": { "_k0": "t2" } }

The last page answers cursor: null. Cursors cannot continue a page that has no LIMIT, mixes OFFSET, or pages through HAVING — those combinations fail closed with a reason. Hit pages (no GROUP BY) do not carry cursors yet.

Parameters #

$1-style positional references bind against the request’s params array — on the parsed tree, never spliced into the text. Numbering starts at $1 ($0 is rejected), values may appear anywhere a literal can, and a referenced-but-unbound $n fails the request:

SELECT _key FROM sq_nulls WHERE price BETWEEN $1 AND $2      -- params: [10, 40]
SELECT _key FROM sq_nulls WHERE tag IN ($1, $2) AND price > $3
SELECT UPPER($1) FROM sq_nulls LIMIT 1

Because binding happens after parsing, one cached plan serves every param set — repeated statements parse once.