query-dialect

# The query dialect **MBQL or SQL?** MBQL (`execute_query`) is the default for everything it can express (`execute_query`'s description lists the operations) and for anything that will sit on a filtered dashboard — an MBQL card wires to dashboard filters as-is; a raw-SQL card must be rewritten with template tags first. SQL (`execute_sql`) is warranted only for window functions, CTEs, set operations, engine-specific functions, an explicit request for SQL, or a structured attempt rejected for a reason you can't fix. Unsure: start in MBQL — the server validates it and names what didn't resolve. A whole-table count needs no field ids at all, just the table id from `list_tables` or `search`: ```json {"lib/type": "mbql/query", "stages": [{"lib/type": "mbql.stage/mbql", "source-table": 5, "aggregation": [["count", {}]]}]} ``` Everything — tables, columns, saved questions, models, metrics, measures, segments — is named by **numeric id**, copied from `browse_data` (`list_tables`, `get_fields`), `search`, or `get_content`; never invented. A wrong name errors loudly; a wrong id that exists resolves to the wrong column *silently*. `get_content`'s `definition` include returns this same shape, so a read definition can be edited and sent back. Loop: author → `execute_query` `validate_only: true` (shape + ids, mints a `query_handle`, runs nothing) → execute → `question_write` with that `query_handle` (saves exactly what ran). ## Shape `{"lib/type": "mbql/query", "database": <db id>, "stages": [...]}`. `execute_query` infers `database` from the first stage; `question_write`'s inline `query` requires it — include it. ```json {"lib/type": "mbql/query", "database": 1, "stages": [{"lib/type": "mbql.stage/mbql", "source-table": 5, "aggregation": [["count", {}]], "breakout": [["field", {"temporal-unit": "month"}, 42]]}]} ``` - First stage only: `source-table` (table id) **or** `source-card` (card id), exactly one; later stages read the previous stage's output. - Optional stage keys: `filters`, `aggregation`, `breakout`, `expressions`, `fields`, `joins`, `order-by`, `limit`. ## Limit and paging `limit: N` on a stage bounds the whole result to its first N rows in `order-by` order. `execute_query`'s `row_limit` is the page size (default 100), not a bound: a truncated page carries `next_cursor`, and each `cursor` call serves the next page until `truncated` is false. A limited query spends its limit down across pages, so its last page arrives `truncated: false` with no `next_cursor` — pagination ends by itself. "The first 400 charges by id" is therefore `order-by` + `limit: 400`, paged at the default size, never an unbounded query stopped after four pages by hand: ```json {"lib/type": "mbql/query", "stages": [{"lib/type": "mbql.stage/mbql", "source-table": 30, "fields": [["field", {}, 2751]], "order-by": [["asc", {}, ["field", {}, 2751]]], "limit": 400}]} ``` ## Two rules 1. **Every clause is `["op", {}, ...args]`** — options map at position 1 even when empty: `["count", {}]`, never `["count"]`. (Repaired server-side, but write it so your query matches later reads.) Never put a stage key (`aggregation`, `filters`, …) at a clause head — clauses go inside those arrays. 2. **A field ref is `["field", {}, <numeric id>]`.** In a later stage, reference a previous stage's column by its **machine name**: `["field", {}, "count"]`. An aggregation's output name is the bare function (`count`, `sum`, `avg`; a second `sum` is `sum_2`) unless its options set `"name"`; a breakout keeps the field's machine name even when bucketed. Never a display label ("Max of Total"). Field options: `temporal-unit` (`"day"`, `"week"`, `"month"`, `"quarter"`, `"year"`, …), `binning` (`{"strategy": "num-bins", "num-bins": 10}` for histograms), `join-alias` (required on every explicitly-joined ref), `source-field` (implicit-FK disambiguation, a field id). Don't breakout the same field twice in one stage (bucketed and raw). ## Filters, aggregation, order-by `filters` entries are ANDed; nest `["or", {}, …]` for OR. ```json "filters": [[">", {}, ["field", {}, 40], 100], ["=", {}, ["field", {}, 61], "Gadget"]], "aggregation": [["sum", {"name": "revenue"}, ["field", {}, 40]]], "order-by": [["desc", {}, ["aggregation", {}, 0]]] ``` - `order-by`: `["asc"|"desc", {}, ref]`. A same-stage aggregation is `["aggregation", {}, <0-based index>]` — **never** `["field", {}, "count"]` in the stage that computes it (validates, then fails or misresolves at execution); name refs to aggregations are for the next stage. - `HAVING` (filter on an aggregation) goes in a next stage, by output name (below). - Relative dates: `["time-interval", {}, <field>, -30, "day"]`, `["time-interval", {}, <field>, "current", "month"]`. An explicit year or date range is an **absolute** `between`, not relative. - Multi-value: `["in", {}, <field>, "a", "b"]` / `["not-in", …]`. - Date arithmetic: `["datetime-diff", {}, a, b, "day"]`, never `-`. - Full catalog: `learn("query-dialect", "operators")`. ## Expressions ```json "expressions": {"Subtotal": ["+", {}, ["field", {}, 40], ["field", {}, 44]]}, "aggregation": [["sum", {}, ["expression", {}, "Subtotal"]]] ``` ## Joins **Implicit FK join** — reference the related table's field directly; with exactly one FK path the server fills in the join (`browse_data` `get_fields` marks FK columns and targets — check before assuming a column lives on the current table): ```json {"lib/type": "mbql.stage/mbql", "source-table": 5, "aggregation": [["count", {}]], "breakout": [["field", {}, 61]]} ``` Ambiguous FK → add `{"source-field": <FK field id on the source table>}` to the ref. No FK path → explicit join: ```json "joins": [{"alias": "Products", "strategy": "left-join", "stages": [{"lib/type": "mbql.stage/mbql", "source-table": 8}], "conditions": [["=", {}, ["field", {}, 43], ["field", {"join-alias": "Products"}, 60]]]}], "breakout": [["field", {"join-alias": "Products"}, 61]] ``` `strategy`: `left-join` (default) / `right-join` / `inner-join` / `full-join`. **Every** joined-column ref carries `{"join-alias": "<alias>"}`, in conditions and downstream. Conditions accept only `=`, `!=`, `<`, `<=`, `>`, `>=`. ## Multi-stage Filter on an aggregation's result in the next stage, by output name: ```json "stages": [ {"lib/type": "mbql.stage/mbql", "source-table": 5, "aggregation": [["count", {}]], "breakout": [["field", {}, 43]]}, {"lib/type": "mbql.stage/mbql", "filters": [[">", {}, ["field", {}, "count"], 10]], "order-by": [["desc", {}, ["field", {}, "count"]]]} ] ``` Window: `offset` sits in `aggregation` and reads another breakout row — month-over-month is `["offset", {"name": "prev_month"}, ["sum", {}, <field>], -1]` against a monthly breakout. ## Saved questions and models as sources `source-card` takes the card's numeric id — never `card__<id>`, URI refs, or entity_ids — and its columns go by output name. The card must be in the same database as the rest of the query. Never fall back to native `{{#id}}` SQL. ```json {"lib/type": "mbql/query", "database": 1, "stages": [{"lib/type": "mbql.stage/mbql", "source-card": 137, "filters": [[">", {}, ["field", {}, "total"], 100]], "limit": 50}]} ``` ## Metrics, measures, segments By numeric id, on a stage whose `source-table` is their base table: metric `"aggregation": [["metric", {}, 42]]`; measure `"aggregation": [["measure", {}, 7]]`; segment `"filters": [["segment", {}, 3]]`. `browse_data` `get_fields` lists each table's with ids. To compose on top of one, `get_content` `include: ["definition"]` returns its clauses in this dialect to inline and extend. A metric's or measure's output column is named for the aggregation inside its definition (`sum`, `count`, `avg`), not for the metric or measure — set `"name"` in its options slot (`["measure", {"name": "revenue"}, 7]`) to name it for a later stage. ## Translating the request - "only / where / for X" is a **filter**; "by / per / for each / over time" is a **breakout**. - "first / top / latest N" is `order-by` + `limit: N` in the stage (see Limit and paging). - Apply every stated constraint; add no analysis the user didn't ask for. ## Don't - Don't guess ids — a wrong id that exists resolves to the wrong column with no error. - Don't order by a same-stage aggregation by name (`["field", {}, "count"]`) — `["aggregation", {}, <index>]`; name refs are for the next stage. - Don't omit the `{}` options slot — the server repairs it, so your query stops matching later reads. - Don't reference a previous stage's column by display label ("Max of Total") — machine name only. - Don't turn "only / where X" into a breakout, or drop a stated constraint once the aggregation is in place. - Don't stop paging while `truncated` is true — follow `next_cursor`, or bound the result with a stage `limit: N`.