/query-dialect

name: query-dialect
description: The query dialect execute_query and question_write's `query` accept — numeric table/field ids, clause shape, filters, aggregation, breakouts, joins, expressions, multi-stage queries, saved-card sources. Read before authoring any non-trivial query, or on a shape / unknown-id rejection. Triggers — "write an MBQL query", "aggregate and group by", "join two tables", "filter then re-aggregate", "month-over-month", "query a saved question or model".

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`:

{"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.

{"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:

{"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.

"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

"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