The query spec

A spec is a JSON object describing one query: which fields to project, how to decorate them, what to filter, which calculations and segments to add. It is stateless; the server knows nothing about it before the request and nothing after. This page is the top-level reference. Each sub-object has its own page.

Top-level keys

Unknown top-level keys are rejected with class Query::Spec::InvalidSpecError and a message starting malformed spec: . The same applies to unknown keys inside a projection, a filter leaf or a segment. There is no top-level sort key: ordering is per projection through order_by.

{"spec": {"projections": [{"field": "category"}], "sort": "category"}}

{"error": {"class": "Query::Spec::InvalidSpecError", "message": "malformed spec: unknown field `sort`, expected one of ..."}}

Field references

Wherever a spec names a field (field in a projection or filter, segment keys and measures, top_n_measure, [Name]@m inside a formula) the reference is a string, or {"uid": "..."}. Resolution runs in this order:

1. Trim, then split once on @. The suffix (@d, @dim, @dimension, @m, @measure) or an explicit field_type key picks the kind. Anything whose last segment starts with d is a dimension; any other non-empty value is a measure. No suffix means either kind.
2. Exact uid match within the allowed kinds.
3. Else name match, trimmed, case-insensitive.
4. Else synonym match, trimmed, case-insensitive.
5. Zero matches: Semantic::NotFound with No field named 'x' in this model. More than one: Semantic::Ambiguous.

Hidden fields resolve like any other. These three projections all point at the same measure:

[{"field": "ws_net_paid"}, {"field": "web net paid"}, {"field": "Web Net Paid@m"}]

Ambiguity

A model may hold a dimension and a measure with the same name (Net Paid as the raw column and Net Paid as its sum). A bare reference then fails:

{"error": {"class": "Semantic::Ambiguous",
 "message": "Field 'Net Paid' is ambiguous — more than one field answers to it. Use Net Paid@d or Net Paid@m to pick the dimension or the measure."}}

Fix it with the suffix or with field_type:

{"field": "Net Paid@m"}
{"field": "Net Paid", "field_type": "measure"}

Fuzzy correction

When a reference of four or more characters matches nothing, every field of the wanted kind is scored by trigram overlap against its name, uid and synonyms. If the best score is at least 0.5 and leads the runner-up by at least 0.2, it is taken silently and reported in the response’s corrections array:

{"spec": {"projections": [{"field": "categry"}, {"field": "web net paid"}]}}

{"sql": "...", "datasource": "...", "datasource_uid": "...", "adapter": "...",
 "corrections": [{"term": "categry", "field_uid": "category", "field_name": "Category", "score": 0.83}]}

Otherwise the Semantic::NotFound message gains Did you mean: A, B, C? with up to three candidates. References shorter than four characters never correct; they resolve exactly or fail. Details on Errors and corrections.

An annotated spec

Monthly web revenue by category over the last year, as a share of the total and as month-over-month change, restricted to products that sold in store, with a margin calculation and the top five categories only.

{
  "name": "Monthly web revenue, top categories",
  "limit": 500,
  "projections": [
    {"field": "date", "alias": "Month", "order_by": "asc",
     "decorators": [{"type": "truncate", "grain": "month"}]},
    {"field": "category"},
    {"field": "ws_net_paid", "alias": "Web Net Paid"},
    {"field": "ws_net_paid", "alias": "Share of Total",
     "decorators": [{"type": "contribute", "partition_refs": ["date"]}]},
    {"field": "ws_net_paid",
     "decorators": [{"type": "temporalize", "transform": "month_over_month", "percent_change": true}]},
    {"alias": "Margin %", "calculation": true, "data_type": "decimal",
     "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)"}
  ],
  "filters": [
    {"field": "date", "predicate": "between", "value": "1y", "value_end": "1d"},
    {"field": "category", "predicate": "top_n", "value": "5", "top_n_measure": "ws_net_paid"}
  ],
  "segments": [
    {"name": "Sold in store", "keys": ["product_name"], "measures": ["net_paid"],
     "filters": [{"field": "net_paid", "predicate": "greater_than", "value": "0"}]}
  ],
  "db_settings": {"final_pass_measure_join_type": "left"},
  "view": {"client_state": "carried back untouched"}
}

Line by line:

• date with a truncate decorator becomes DATE_TRUNC('month', ...); without the decorator a date projection is the raw column. The explicit alias Month overrides the derived Month(Date).
• The three ws_net_paid projections are the same measure three times: plain, as a percent of the month’s total (partition_refs restricts the total to each month), and as a month-over-month percent change. The last one carries no alias, so its decorator lends one: LM(Web Net Paid), prefixed with % because percent_change is set. See Projections and decorators.
• Margin % is an inline calculation: plain SQL around [Name]@m measure references, computed over the aggregated measures. See Calculations.
• The between filter uses relative dates: 1y snaps to the start of last year, 1d is yesterday. top_n keeps the five categories ranked by ws_net_paid. See Filters.
• The segment is a population of product_name values with positive store sales; listing net_paid under measures routes membership through store_sales. With no apply_to it constrains the whole query. See Segments.
• db_settings turns the blend’s final join into a LEFT JOIN; view and limit are carried back to your client untouched.

The SQL for this spec is a blend (web and store measures) with a segment CTE, a contribution total CTE, a shifted-date CTE for the month-over-month measure and a top-n ranking CTE, merged in one final SELECT. Each piece is shown on its own page with the engine’s actual output.

Sending it

Wrap the spec in the request envelope and POST it; the shape of the HTTP exchange is on the Query API page.

curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_..." \
  -H "Content-Type: application/json" \
  -d @spec.json

zsql sql --spec spec.json --context ctx.json

const res = await fetch("https://app.0sql.io/projects/tpcds/branches/main/sql", {
  method: "POST",
  headers: { Authorization: `Bearer ${key}`, "Content-Type": "application/json" },
  body: JSON.stringify({ spec, context }),
});
if (!res.ok) {
  const { error } = await res.json();
  throw new Error(`${error.class}: ${error.message}`);
}
const { sql } = await res.json();

Next steps

• Projections and decorators
• Filters
• Shorthand expressions for the one-line form of the same spec
• Example requests