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 https://app.0sql.io/projects/{uid}/branches/{branch}/sql
Authorization: Bearer zqk_...
Content-Type: application/json

• {uid} is the project uid from project.yml, {branch} the deployed branch. This section uses tpcds and main.
• Both query keys (zqk_) and personal keys (zsk_) may call it. Query keys are read only and work on the projects and branches they are granted; personal keys do whatever their user may do. See Accounts and keys.
• A branch that has no deployment answers 404 NotFound.

Request envelope

Send a spec or a one-line shorthand expr, 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"]
}

Error envelope

Every error has the same shape and an HTTP status that follows the class:

{"error": {"class": "Semantic::NotFound", "message": "No field named 'revenue' in this model."}}

Spec and planner errors are 422; missing context, bad shorthand and bad envelopes are 400; key problems are 401 and 403. 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 https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_..." \
  -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 --expr 'ws_net_paid as Web Net Paid, ws_sold_date as Web Sold Date, net_paid as Net Paid, category in (men, children)'

const res = await fetch("https://app.0sql.io/projects/tpcds/branches/main/sql", {
  method: "POST",
  headers: {
    Authorization: `Bearer ${process.env.ZSQL_API_KEY}`,
    "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]@m formula 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 WHERE and CASE.
• Shorthand expressions: the one-line grammar behind expr, the CLI and the playground.
• Explain: POST .../explain, timings, phases and the node tree.
• Discovery: POST .../explore, GET .../fields, .../tables, .../branches/{branch} and GET /projects.
• Errors and corrections: every class and status, the exact messages, the corrections array.

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.