The 0sql expression language
The 0sql expression language is a shorthand for the query spec JSON. One line of comma-separated items — projections, filters, calculations, segments, settings — lowers to the same spec you would have written by hand, and every attribute the planner reads has a spelling. It exists for people typing queries (the CLI, the repl, the playground) and for agents, where a line costs about a fifth of the tokens the equivalent JSON does.
month(date), category, web net paid, yoy_pct(web net paid) as Growth desc,
date between 2024-01-01 and 2024-12-31, category top 5 by web net paid,
include (customer id, net paid > 0), limit 100
Where it is accepted
{"expr": "..."}in place ofspecinPOST .../sql,.../explainand.../explore. The response then carriesspec(the spec the line produced) anditems(one line per item saying how it was read). Parse errors come back as400classShorthand.zsql sql '...',zsql explain '...',zsql explore '...'— the line is a positional argument, and several words are joined with spaces, so quoting is optional. The CLI parses locally and sends aspec, printing the item descriptions to stderr.zsql spec '...'prints both without calling the server, andzsql spec --helpcarries the whole summary.- The repl (
zsqlinside a project): any line not starting with{is an expression line. Tab completes fields, functions and keywords by the grammar;.helpprints the forms. - The playground on this site.
The shape
Items are separated by commas or newlines. Each item is read by its leading structure — the first reading that parses wins:
| Starts with | Item |
|---|---|
limit, hints | a query setting |
include, exclude, expand | a segment |
{ | raw spec JSON merged into the query |
( | a filter group |
calc( | a calculation |
| a name, then a predicate | a filter |
| a name, or a function around one | a projection |
Classification needs what the keyword needs to follow it, so limit alone, include tax and month are projections of fields with those names. Only the reserved words below are reserved everywhere.
Fields and values
A field is bare words, [bracketed] or "quoted". A value is bare text or 'single quoted'. That asymmetry is the whole escaping story: double quotes always name a field, single quotes always mark a value.
| Written | Read as |
|---|---|
category | a bare name |
[Sign In Date] | bracketed, because in is reserved |
"Is Active" | quoted, same reason |
web net paid@m | the measure, where a name is both a dimension and a measure |
'Rock and Roll' | a value, because it holds and |
- Names and keywords fold case.
Month(Date)andmonth(date)are the same line. A name may be a field name or a uid. - Fourteen reserved words end a bare name wherever they appear:
and or not in is between top contains starts ends keyword as asc desc. Bracket or quote a name that holds one. The parser reports the word, where it stopped, and the spelling to use. - Context words are keywords only in position and plain words everywhere else:
byaftertop Nand inside a function’s parentheses;orderonly beforeby;nullafteris;withafterstarts,endsand anexpand (...)group;for,left,inner,dedupeandneq/lt/nonein a segment’s tail. Soorder date,created byandnull countare bare names, andorder byis the clause. - A hyphen is a letter.
2024-01-01,28dand the uidweb-net-paidare each one word. Subtraction exists only insidecalc(...). @dand@mattach with no space to any field form —"Net Paid"@m,[Net Paid]@m— and lower tofield_type.- Values are typed on the way out. A value that is wholly an integer or a decimal becomes a JSON number,
trueandfalsebecome booleans, everything else stays the text written. A leading zero keeps a value text, so02134stays a postal code;1.10becomes1.1, and a single-quoted'1.10'stays text.
Projections
A projection is a field, optionally inside decorator functions, with an optional as Alias and asc/desc.
category
web net paid as Paid desc
month(date)
extract(day_of_week, date) as Weekday
yoy_pct(web net paid) as Growth
share(web net paid by category)
moving_avg(web net paid, 7 by category order by date)
Decorator functions
| Family | Spelling | Spec |
|---|---|---|
| truncate | hour(x) day(x) week(x) month(x) quarter(x) year(x), also raw(x) minute(x) second(x) millisecond(x) | {"type": "truncate", "grain": "<fn>"} |
| extract | extract(part, x), part one of minute hour day_of_month day_of_week day_of_year week_of_year month quarter year day_name month_name year_month | {"type": "extract", "extract": "<part>"} |
| temporalize | yoy(x) qoq(x) mom(x) wow(x) dod(x) for the prior period’s value; yoy_pct(x) and the rest for the relative change | {"type": "temporalize", "transform": "year_over_year"}, plus "percent_change": true |
| contribute | share(x), share(x by a, b); share_filtered(x by a) keeps the query’s filters in the total | {"type": "contribute"} plus partition_refs, and ignore_partition_filters: false for share_filtered |
| window, frame | running_sum running_avg running_min running_max running_count, and the five moving_ forms | {"type": "window", "mode": "running" or "moving", plus "function" and "size" |
| window, rank | rank dense_rank row_number percent_rank cume_dist first_value last_value, ntile(x, 4), lag(x, 1), lead(x, 1) | {"type": "window", "mode": "running", "function": "<fn>"} plus buckets or offset |
| customize | custom('sql with @expression', x) | {"type": "customize", "custom_sql": "..."} |
One spelling per thing: there is no pct(x) beside share(x), no dow(x) beside extract(day_of_week, x), no postfix form.
Partition and order. by names the partition, order by the ordering, both inside the function’s parentheses:
running_sum(web net paid by category)
rank(web net paid by category order by date desc)
share(web net paid by category, region)
A reference after by or order by is a projected dimension, named by its uid, name or alias, never an expression — write order by date, not order by month(date), because the projection already carries the truncation. A reference that names no projected dimension is a 422, and a reference to a measure says so; it is not silently dropped.
A moving window’s number is rows in the frame, the current row included, which is what “a seven-day moving average” means. The spec’s size counts the rows before the current one, so moving_avg(x, 7) lowers to "size": 6. moving_avg(x, 1) is an error: a frame holds at least two rows. For ntile, lag and lead the number is the spec’s buckets or offset as written.
Functions nest. share(yoy(web net paid)) is a share of the year-over-year value. The parser does not type-check: yoy(month(date)) parses and then fails lowering, because temporalize is measure-only. Errors of that kind come back as 422 with the spec’s validation message, not as class Shorthand.
Calculations
A calculation is calc(...) around its SQL, and nothing else is — = only ever means a filter, so the parser never reads SQL to classify an item.
calc(sum([Web Net Paid]@m) / sum([Net Paid]@m)) as Web Share
calc(count(*), integer) as Rows
calc(([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)) as Margin %
yoy_pct(calc(sum([Web Net Paid]@m) / sum([Net Paid]@m))) as Mix growth desc
calc(1000) as Target
- The alias follows as
as; with none, the formula’s text is its own alias. - The result type is an optional last argument after a top-level comma:
string integer decimal date date_time boolean bigint. A top-level comma followed by anything else is an error naming the types. - The closing parenthesis is found by balancing parentheses and single quotes, so
coalesce([A]@m, 0)and'a, b'keep their commas. - Inside a formula, references keep the spec’s own bracket forms —
[Name]@m,[Name]@d,[Alias]— because double quotes inside SQL are column identifiers, which calculations forbid. A uid is bracketed too,[web-net-paid]@m, so its hyphens are never read as subtraction. See Calculations for the formula grammar. - A decorator wraps
calc(...)exactly as it wraps a field, and nothing is peeled.
Filters
category = Books
web net paid > 100
date between 2024-01-01 and 2024-12-31
date >= 28d
category in (Books, Music)
category not in (Toys, Games)
product name contains made in china
product name not starts with X
product name keyword (sale, clearance)
date is not null
category top 10 by web net paid
web net paid@m > 1000
"Is Active" = true
[Sign In Date] >= 7d
title = 'Rock and Roll'
title = 'Toys, games (new)'
(category = Books or category = Music) and web net paid > 1000
Predicates emitted: equals, does_not_equal, greater_than, less_than, greater_than_or_equal_to, less_than_or_equal_to, in_list, exclude_list, between, contains, does_not_contain, starts_with, does_not_start_with, ends_with, does_not_end_with, is_null, is_not_null, top_n, keyword. The spec’s custom predicate has no spelling, because the planner does not implement it.
Trees. A comma between items is AND, as before. and, or and parentheses now build the spec’s tree, and a query whose filters hold no or lowers to the flat array, so the common case keeps its old shape. See Filters for what the planner does with each.
not goes before the predicate word and only before the four that negate in the spec — in, contains, starts with, ends with — with is not null accepted as the English. not = is an error pointing at !=; not between and not (...) are errors, because the spec has neither.
Values run as plain text to the next separator, so one may hold in, top or desc unquoted. Single-quote a value that holds a comma, a parenthesis, and or or. Inside a list a value runs to the next comma or the closing parenthesis, so in (Rock and Roll, Jazz) is two values without quotes. in and keyword always take a parenthesised list, even of one value.
A measure filter is a HAVING, as in the spec.
Segments
include (customer id, net paid > 0) as Catalog buyers
exclude (customer id, web net paid > 1000) for net paid
include (customer id, web net paid@m)
expand (customer id, category = Books) with (category as Also bought) left dedupe none as Book pairs
Inside the parentheses, items are read by the same rules as the query: anything with a predicate is a filter (trees included), and a lone name is a key. The lowering has no model, so it cannot tell a measure from a dimension by name — mark a measure that defines membership through its fact table with @m.
include and exclude are the spec’s modes. expand is the expanding segment, whose carried dimensions sit in with (...) because they are not keys; each may take as for its column name. Tails come in any order: as Name, for measure or for (a, b), left/inner, dedupe neq/lt/none. A segment name holding for, left, inner or dedupe is quoted. See Segments.
Settings and the escape hatch
limit 100
hints (web_sales, item)
{"name": "Monthly growth", "db_settings": {"week_start_day": 1}}
web net paid {"format": {"type": "currency"}}
limit and hints are the two settings that change the plan and earn a keyword. hints names tables the resolver should prefer, by uid or by name, as one name or a parenthesised list. A hint that names no table refuses the query with the nearest names, and one that names two says both and asks for a uid — a hint is a preference about routing, but a hint that resolves to nothing is a mistake rather than a preference.
Everything else the spec carries — name, description, classification, view, db_settings, a projection’s format — goes through braces. A {...} item is merged into the query object; a {...} directly after an item, with no comma between, is merged into that item’s object. Merging is deep for objects, appends for arrays and replaces scalars. Raw JSON is merged after the native items wherever it stands, and a limit given both ways is refused rather than overridden.
That hatch is what makes parity complete: 45 of the spec’s 51 planned or carried attributes have a native spelling, and all 51 are reachable. The spec’s axis and hidden are not handled at all — both are leaving the spec, and the planner’s one use of axis, choosing an automatic partition, is replaced by an explicit by.
Errors
Every error names what was expected and where, with a column (and a line, on a multi-line input):
$ zsql spec 'Is Active = true'
`Is` is a reserved word and cannot start a name; write [Is Active] or "Is Active" (column 1)
$ zsql spec 'title = Rock and Roll'
after `and`, expected `field predicate value`; a value holding and/or is single-quoted (column 18)
$ zsql spec 'monthly(date)'
unknown function `monthly`; the help lists the functions, and a field whose name holds a parenthesis is bracketed (column 8)
$ zsql spec 'category in (Books, "Music")'
a value is single-quoted; double quotes name a field (column 21)
$ zsql spec 'moving_avg(web net paid, 1)'
a moving frame holds at least two rows: the current one and one before (column 26)
$ zsql spec 'limit 2.5'
limit needs a whole number of rows (column 7)
The parser commits once it sees an opener — limit, a segment’s (, a function’s (, a comparison operator, between, top, as, by, order by, the comma inside calc(, [, ", ' — so the error is reported inside the construct that was begun rather than as a late “expected a comma”. Raw JSON goes to the JSON parser once its braces balance, and its line and column are mapped back to your input.
Over the API these are 400 class Shorthand. Everything a model is needed for — whether a name is a dimension or a measure, whether a partition reference is projected, whether a predicate suits the type — is the spec lowering’s business and comes back as 422. See Errors and corrections.
Examples
Each line with the spec it produces, as zsql spec prints it.
- Two projections and a sort.
category, web net paid desc
{"projections": [{"field": "category"}, {"field": "web net paid", "order_by": "desc"}]}
- A truncated date, a percent change and an alias.
month(date), web net paid, yoy_pct(web net paid) as Growth
{"projections": [
{"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]},
{"field": "web net paid"},
{"field": "web net paid", "alias": "Growth",
"decorators": [{"type": "temporalize", "transform": "year_over_year", "percent_change": true}]}]}
- A date range and a list. Numbers and booleans are emitted as JSON scalars; everything else is the text written.
category, web net paid, date between 2024-01-01 and 2024-12-31, category in (Books, Music)
{"projections": [{"field": "category"}, {"field": "web net paid"}],
"filters": [
{"field": "date", "predicate": "between", "value": "2024-01-01", "value_end": "2024-12-31"},
{"field": "category", "predicate": "in_list", "value": "Books, Music"}]}
- Top N by a measure.
category, web net paid, category top 5 by web net paid
{"projections": [{"field": "category"}, {"field": "web net paid"}],
"filters": [{"field": "category", "predicate": "top_n", "value": 5, "top_n_measure": "web net paid"}]}
- A share within a partition.
category, product name, web net paid, share(web net paid by category) as Share of Category
{"projections": [
{"field": "category"}, {"field": "product name"}, {"field": "web net paid"},
{"field": "web net paid", "alias": "Share of Category",
"decorators": [{"type": "contribute", "partition_refs": ["category"]}]}]}
- A moving average over a partition, ordered by the projected date. Seven rows in the frame is
"size": 6.
day(date), category, web net paid, moving_avg(web net paid, 7 by category order by date) as 7 day avg
{"projections": [
{"field": "date", "decorators": [{"type": "truncate", "grain": "day"}]},
{"field": "category"}, {"field": "web net paid"},
{"field": "web net paid", "alias": "7 day avg",
"decorators": [{"type": "window", "mode": "moving", "function": "avg", "size": 6,
"partition_refs": ["category"], "order_by": {"date": "asc"}}]}]}
- An or/and tree. With no
oranywhere, filters stay the flat array.
category, web net paid, (category = Books or category = Music) and web net paid > 1000
{"projections": [{"field": "category"}, {"field": "web net paid"}],
"filters": {"and": [
{"or": [{"field": "category", "predicate": "equals", "value": "Books"},
{"field": "category", "predicate": "equals", "value": "Music"}]},
{"field": "web net paid", "predicate": "greater_than", "value": 1000}]}}
- Two calculations, one with a result type.
category, calc(sum([Web Net Paid]@m) / sum([Net Paid]@m)) as Web Share, calc(count(*), integer) as Rows
{"projections": [{"field": "category"}],
"calculations": [
{"calculation": true, "alias": "Web Share", "sql": "sum([Web Net Paid]@m) / sum([Net Paid]@m)"},
{"calculation": true, "alias": "Rows", "sql": "count(*)", "data_type": "integer"}]}
- A segment, named in the tail.
customer id, web net paid, include (customer id, net paid > 0) as Catalog buyers
{"projections": [{"field": "customer id"}, {"field": "web net paid"}],
"segments": [{"name": "Catalog buyers", "mode": "include", "keys": ["customer id"],
"filters": [{"field": "net paid", "predicate": "greater_than", "value": 0}]}]}
- An expanding segment for pair analysis.
category, web net paid, expand (customer id, category = Books) with (category as Also bought) left dedupe none as Book pairs
{"projections": [{"field": "category"}, {"field": "web net paid"}],
"segments": [{"name": "Book pairs", "keys": ["customer id"],
"filters": [{"field": "category", "predicate": "equals", "value": "Books"}],
"expanding": [{"field": "category", "as": "Also bought"}],
"join": "left", "pair_dedupe": "none"}]}
- A measure-level exclusion.
category, net paid, exclude (customer id, web net paid > 1000) for net paid
{"projections": [{"field": "category"}, {"field": "net paid"}],
"segments": [{"mode": "exclude", "keys": ["customer id"], "apply_to": ["net paid"],
"filters": [{"field": "web net paid", "predicate": "greater_than", "value": 1000}]}]}
- A limit, a relative date and a routing hint.
web net paid, date >= 28d, limit 10, hints (web_sales, item)
{"projections": [{"field": "web net paid"}],
"filters": [{"field": "date", "predicate": "greater_than_or_equal_to", "value": "28d"}],
"limit": 10, "hints": ["web_sales", "item"]}
- Both uses of the escape hatch: a query attribute and a projection attribute.
category, web net paid {"format": {"type": "currency"}}, {"name": "Paid by category"}
{"name": "Paid by category",
"projections": [{"field": "category"},
{"field": "web net paid", "format": {"type": "currency"}}]}
- A measure filter, a quoted field and a typed value.
"Is Active" = true, customer id, web net paid@m > 100
{"projections": [{"field": "customer id"}],
"filters": [
{"field": "Is Active", "predicate": "equals", "value": true},
{"field": "web net paid", "field_type": "measure", "predicate": "greater_than", "value": 100}]}
A newline separates items exactly as a comma does, so the same query may be written down a page:
month(date), category
web net paid, share_filtered(web net paid by category) as Share
date >= 1y, category in (Books, Music)
include (customer id, net paid > 0) as Catalog buyers
limit 500
Inside a list, a function’s arguments or a formula, a newline is just whitespace, so a long in (...) or a long formula may be wrapped.
Sending an expr
curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/sql \
-H "Content-Type: application/json" \
-d '{"expr": "month(date), category, web net paid, category = Books"}' zsql sql 'month(date), category, web net paid, category = Books' 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({ expr: "month(date), category, web net paid, category = Books" }),
});
const { sql, spec, items } = await res.json(); The response includes the generated spec and items such as projection month(date) and filter category = Books, so a client can show the user how the line was read before running the SQL.
Next steps
- The query spec for the JSON this lowers to, and the few attributes that only the hatch reaches
- Querying with zsql for the repl, its completion and its dot-commands