Query API
The contract is small. You send a query spec (what to project, filter, calculate and segment) together with a security context describing the caller, and 0sql returns one SQL statement for the datasource the model lives in. Planning takes microseconds. Nothing is executed and nothing is stored: your application runs the SQL against its own warehouse. The same planner also runs Strata.
Endpoint
POST http://127.0.0.1:3699/projects/{uid}/branches/{branch}/sql
Content-Type: application/json
{uid}is the project uid fromproject.yml,{branch}the deployed branch. This section usestpcdsandmain.- There is no key and no
Authorizationheader: the server has no accounts and answers whoever can reach it. Your application is the caller, and it is what stands between your users and this endpoint; see securing access. - A branch that has no deployment answers
404 NotFound.
Request envelope
Send a spec or a one-line expr in the expression language, plus an optional context. If both spec and expr are present, spec wins. Neither gives 400 Invalid with the message give a spec or an expr.
{"spec": {"projections": [{"field": "category"}, {"field": "ws_net_paid"}]},
"context": {"email": "tank@matrix.com", "groups": [{"name": "CC-TMNT", "tags": ["call_center_id:TMNT"]}]}}
{"expr": "category, web net paid, category = Books",
"context": {"email": "tank@matrix.com"}}
The context is required on a branch that has security policies (otherwise 400 ContextRequired) and optional elsewhere. See Security context.
Response envelope
{
"sql": "SELECT ...",
"datasource": "Warehouse",
"datasource_uid": "warehouse",
"adapter": "postgres",
"corrections": [{"term": "departmnt", "field_uid": "department", "field_name": "Department", "score": 0.8}],
"spec": {"projections": [{"field": "category"}]},
"items": ["projection category"]
}
| key | always | meaning |
|---|---|---|
sql | yes | the single statement to run |
datasource, datasource_uid, adapter | yes | which warehouse the statement is for and its dialect |
corrections | only when non-empty | field references that were fuzzy-corrected; see Errors and corrections |
spec | only for expr requests | the spec the expression line produced |
items | only for expr requests | how each comma-separated item was read |
Error envelope
Every error has the same shape and an HTTP status that follows the class:
{"error": {"class": "FieldNotFound", "message": "No field named 'revenue' in this model."}}
Spec and planner errors are 422; missing context, an expr that does not parse and bad envelopes are 400; a server too busy to take the request answers 503. The full table is on Errors and corrections.
A complete example
Two measures from different fact tables, drilled across a date they share. Web Net Paid lives on web_sales, Net Paid on store_sales; both facts join item, so the category filter applies to each.
curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/sql \
-H "Content-Type: application/json" \
-d '{
"spec": {
"name": "Query One",
"projections": [
{"field": "ws_net_paid", "alias": "Web Net Paid"},
{"field": "ws_sold_date", "alias": "Web Sold Date"},
{"field": "net_paid", "alias": "Net Paid"}
],
"filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]
}
}' zsql sql 'ws_net_paid as Web Net Paid, ws_sold_date as Web Sold Date, net_paid as Net Paid, category in (men, children)'The CLI parses the line locally and sends a spec. The quoted "books,com" item from the JSON cannot be written as an expression — a list value cannot hold a comma, and the parser says so rather than lowering a list the planner will misread; see filters.
const res = await fetch("http://127.0.0.1:3699/projects/tpcds/branches/main/sql", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
spec: {
name: "Query One",
projections: [
{ field: "ws_net_paid", alias: "Web Net Paid" },
{ field: "ws_sold_date", alias: "Web Sold Date" },
{ field: "net_paid", alias: "Net Paid" },
],
filters: [{ field: "category", predicate: "in_list", value: 'men ,children, "books,com"' }],
},
}),
});
const { sql, datasource, adapter } = await res.json();
// run `sql` with your own warehouse client The sql that comes back:
WITH ag7098b0d0901f2eb16d14f9356f0bb2a0 AS (
SELECT
T0."ss_sold_date_sk" AS "dimed56b67",
sum(T0."ss_net_paid") AS "msr621f67c"
FROM
store_sales T0
JOIN item T1
ON T0.ss_item_sk = T1.i_item_sk
WHERE
LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
T0."ss_sold_date_sk"
), ag29130fae5cf6548d6ddb1bd37298b407 AS (
SELECT
sum(T0."ws_net_paid") AS "msr501e4a8",
T0."ws_sold_date_sk" AS "dimed56b67"
FROM
web_sales T0
JOIN item T1
ON T0.ws_item_sk = T1.i_item_sk
WHERE
LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
T0."ws_sold_date_sk"
)
SELECT
A0.msr501e4a8 AS "Web Net Paid",
COALESCE(A1.dimed56b67, A0.dimed56b67) AS "Web Sold Date",
A1.msr621f67c AS "Net Paid"
FROM
ag29130fae5cf6548d6ddb1bd37298b407 A0
FULL OUTER JOIN ag7098b0d0901f2eb16d14f9356f0bb2a0 A1
ON A1.dimed56b67 = A0.dimed56b67
The two measures come from two fact tables with different grains, so summing them in one FROM would fan out the rows; the planner aggregates each fact in its own CTE at the shared date grain first. The final pass joins the two aggregates on that date with a FULL OUTER JOIN so a day with web sales but no store sales (or the reverse) still appears, with COALESCE picking the date from whichever side has it. Your application takes this statement and runs it against the warehouse named in datasource. The join type is a dialect setting (final_pass_measure_join_type) you can override per query through db_settings; see The query spec and Extended blending groups for how such blends are modelled.
In this section
- The query spec: every top-level key, how field references resolve, a full annotated spec.
- Projections and decorators: projection keys, alias derivation, truncate, extract, temporalize, contribute, window and customize with their SQL.
- Filters: flat lists and and/or trees, every predicate, list and date values, top-n, measure filters as
HAVING. - Calculations: the
[Name]@mformula grammar, measure versus dimension calculations, complex measures. - Segments: populations keyed on dimensions, query-level versus measure-level, exclude, expanding segments for pair analysis.
- Security context: the context JSON and how policies turn it into
WHEREandCASE. - The expression language: the shorthand for the spec behind
expr, the CLI and the repl. - Explain:
POST .../explain, timings, phases and the node tree. - Discovery:
POST .../explore,GET .../fields,.../tables,.../branches/{branch}andGET /projects. - Errors and corrections: every class and status, the exact messages, the corrections array.
For a worked application rather than a page of keys, the chat dashboard demo puts all of this behind an agent: GET /fields feeds the prompt, POST /sql plans what the model writes, and the dashboard stores specs rather than SQL. The repo runs end to end on a warehouse it ships with.
Two sibling routes take the same request body: POST .../explain returns the SQL plus the plan (Explain) and POST .../explore returns the fields a query can still add (Discovery). The full route list is in the API reference, and the CLI side is on Querying with zsql.