SQL & PRQL #

Pizza accepts SQL-shaped queries over collections through two text frontends. Both lower into the same execution pipeline as the search DSL — same authz, same lanes, same engine — and answer with a frozen tabular envelope ready for table rendering:

• POST /_sql — a SQL-92-shaped subset with Pizza extension functions for full-text, vectors and relevance.
• POST /_prql — PRQL pipelines, compiled by a pinned prqlc and executed as the equivalent SQL (see PRQL queries).

The same question in both frontends — the PRQL pipeline compiles to the equivalent SQL and rides the identical pipeline, so both answer the same frozen envelope:

POST /_sql
{ "query": "SELECT _key, price FROM sq_nulls WHERE price > 15 ORDER BY price DESC LIMIT 2" }

POST /_prql
{ "query": "from sq_nulls | filter price > 15 | sort {-price} | take 2 | select {_key, price}" }

The transform-by-transform catalog (filter, sort, take, select, group) with worked examples and the pinned compiler’s boundaries lives in PRQL queries.

Try both live in the web console (⌘/Ctrl+Shift+O from anywhere) — bare SELECT/from statement blocks run with keyword and schema completion, $1 params and PIT snapshot pinning behind the sql options popover, and keyset cursor paging via the table’s Next page button. In the docs, every tutorial lesson pairs its DSL examples with the equivalent SQL statement in an in-browser playground, and the playground page carries the full interactive surface with its DSL⇄SQL toggle.

The clause-by-clause walk with runnable examples lives in SQL syntax & examples.

Request and response #

POST /_sql
{
  "query": "SELECT _key, tag, price FROM sq_nulls WHERE price > $1 ORDER BY _key LIMIT 20",
  "params": [10],
  "snapshot_id": "pit-id-from-open",
  "cursor": null
}

Statements are capped at 256 KiB — the surface is for statements, not payloads; oversized requests fail with a 400 before any parsing. Both /_sql and /_prql write into the same search-audit ring as /_search (GET /_node/_local/search_audit), recording the source text you submitted.

The response envelope is frozen:

{
  "columns": [{ "name": "_key", "type": "string" }, { "name": "price", "type": "int" }],
  "rows": [["n2", 20], ["n4", 40]],
  "cursor": { "_k0": "n4" }
}

cursor is non-null only when the plan sorts grouped rows in the composite order (every GROUP BY key ascending, or no ORDER BY) with a LIMIT — that keyset walks the pages without overlap. OFFSET pagination serves hit-lane pages only and cannot combine with cursors.

EXPLAIN <select> answers the LOWERING TRACE — the compiled plan as the DSL a /_search would run, plus the would-be answer’s column labels. Nothing executes:

{
  "columns": ["tier", "COUNT(*)"],
  "query": {
    "query": { "match_all": {} },
    "aggs": { "_g": { "terms": { "field": "tier", "size": 10000 } } },
    "size": 0
  }
}

A cursor on an EXPLAIN is rejected (it answers a plan, not pages).

What the SQL subset covers #

Not in the subset today: JOIN across collections (planned), subqueries, CTE (WITH), window functions, set operations, and SELECT DISTINCT (dedupe with GROUP BY — COUNT(DISTINCT …) is supported). Statements outside the subset fail closed with a source-positioned error — never a silently different answer.

Errors #

Plan and compile errors answer 400 with the reason and a 1-based position against the submitted text — the same contract on both frontends, distinguished by type:

{
  "error": {
    "type": "sql_error",
    "reason": "…",
    "position": { "line": 1, "column": 24 },
    "span": { "start": 23, "end": 25 }
  }
}

/_prql compile failures carry "type": "prql_error" with the position mapped onto the PRQL source. Statements are cached by text (parsed plans on /_sql, compiled SQL on /_prql), so identical statements parse once.