Shorthand expressions

The shorthand is a one-line form of the spec: comma-separated items, each a projection, a filter or a calculation. It exists for people typing queries (the CLI, the repl, the playground) and for agents that find a line easier to produce than JSON. Everything it can say, the JSON spec can say; the reverse is not true.

month(date) as Month asc, category, web net paid, category in (Books, Music), date >= 1y

Where it is accepted

  • {"expr": "..."} in place of spec in POST .../sql, .../explain and .../explore. The response then carries spec (the generated spec) and items (one line per item saying how it was read). Parse errors come back as 400 class Shorthand.
  • zsql sql --expr '...', zsql explain --expr, zsql explore --expr. The CLI parses locally and sends a spec, printing the item descriptions to stderr. zsql spec '...' prints the spec without calling the server.
  • The repl (zsql inside a project): any line not starting with { is shorthand.
  • The playground in the console.

Grammar

Splitting. The line is split on commas that are outside quotes, square brackets and parentheses. An empty line is nothing to plan.

Classification of each item, in order:

  1. Escaped field. [Name], [Name]@m, [Name]@d (also @dim, @measure), "Name" or 'Name' as the whole item is a projection of that field and nothing else. The inner text may not contain [, ], " or '.
  2. Calculation. Alias = formula or Alias := formula, where the formula contains [, the alias is non-empty, contains none of < > ! =, and is not itself a bracketed or quoted field. Produces {"alias", "sql", "calculation": true}. Operator and keyword searches never look inside bracketed or quoted spans.
  3. Filter. Checked on the lower-cased text, in this order: is not null, is null (suffixes); not in (...), in (...) (items split on commas, each unquoted, re-joined with , ); between a and b; top N [by measure] (N must be an integer, else top needs a count, as in `category top 10 by revenue` ); contains, starts with, ends with, each with an optional not or does not before the keyword; then the operators >=, <=, !=, <>, =, >, <. The value is unquoted.
  4. Projection. Optional trailing asc / desc becomes order_by; optional as Alias becomes alias; then nested decorator functions f(g(field, args...)), emitted innermost first in decorators. An unknown function fails with '...': unknown function f(); see .help.
  5. Unnamed calculation. If that fails and the text contains [, it is a calculation: formula as Alias, or, with no alias, the formula is its own alias.

Filter predicate names emitted: is_not_null, is_null, exclude_list, in_list, between, top_n, contains, does_not_contain, starts_with, does_not_start_with, ends_with, does_not_end_with, greater_than_or_equal_to, less_than_or_equal_to, does_not_equal, equals, greater_than, less_than.

There is no and/or in the shorthand. Filters are always a flat AND list; web net paid > 100 and category = Books parses as one filter on a field called web net paid > 100 and category. Use the JSON tree for OR.

Decorator functions

shorthandJSON
raw(x), millisecond(x), second(x), minute(x), hour(x), day(x), week(x), month(x), quarter(x), year(x){"type": "truncate", "grain": "<fn>"}
dow(x), dom(x), doy(x), woy(x), hr(x), min(x), mon(x), qtr(x), yr(x), day_name(x), month_name(x), year_month(x), and the long forms day_of_week(x), day_of_month(x), day_of_year(x), week_of_year(x){"type": "extract", "extract": "<full name>"}
yoy(x), qoq(x), mom(x), wow(x), dod(x){"type": "temporalize", "transform": "year_over_year" ...}
yoy_pct(x), qoq_pct(x), mom_pct(x), wow_pct(x), dod_pct(x)the same with "percent_change": true
pct(x), share(x), contribute(x), contribution(x), each optionally (x, dim, ...){"type": "contribute"} plus "partition_refs": [dims] when given
running_<fn>(x[, dims...]){"type": "window", "mode": "running", "function": "<fn>"} plus "partition_refs" for the dims
moving_<fn>(x, N[, dims...]){"type": "window", "mode": "moving", "function": "<fn>", "size": N} plus "partition_refs"

<fn> is any window function: sum, avg, min, max, count, row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value. There is no shorthand for the customize decorator.

The parser does not type-check. yoy(month(date)) parses fine 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.

Examples

Each line with the spec it produces, as emitted by the parser.

  1. Two projections.
category, web net paid
{"projections": [{"field": "category"}, {"field": "web net paid"}]}
  1. A truncated date, an alias and a sort.
month(date), ws_net_paid as Web Paid desc
{"projections": [
  {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]},
  {"field": "ws_net_paid", "alias": "Web Paid", "order_by": "desc"}]}
  1. Nested functions, innermost first in the output. This one parses but fails lowering (temporalize on a dimension).
yoy(month(date)) as Growth desc
{"projections": [{"field": "date", "alias": "Growth", "order_by": "desc",
  "decorators": [{"type": "truncate", "grain": "month"}, {"type": "temporalize", "transform": "year_over_year"}]}]}
  1. Percent change and an equality filter.
yoy_pct(web net paid), category = Books
{"projections": [{"field": "web net paid", "decorators": [{"type": "temporalize", "transform": "year_over_year", "percent_change": true}]}],
 "filters": [{"field": "category", "predicate": "equals", "value": "Books"}]}
  1. A list and a date range. Filters alone are a valid parse; the server then fails with At least one projection required.
category in (Books, Music), date between 2024-01-01 and 2024-12-31
{"projections": [], "filters": [
  {"field": "category", "predicate": "in_list", "value": "Books, Music"},
  {"field": "date", "predicate": "between", "value": "2024-01-01", "value_end": "2024-12-31"}]}
  1. A quoted value and a null check.
product name contains 'super', date is not null
{"projections": [], "filters": [
  {"field": "product name", "predicate": "contains", "value": "super"},
  {"field": "date", "predicate": "is_not_null"}]}
  1. Top N by a measure.
category top 5 by web net paid
{"projections": [], "filters": [{"field": "category", "predicate": "top_n", "value": "5", "top_n_measure": "web net paid"}]}
  1. A named calculation.
Share = sum([Web Net Paid]@m) / sum([Net Paid]@m)
{"projections": [], "calculations": [{"alias": "Share", "sql": "sum([Web Net Paid]@m) / sum([Net Paid]@m)", "calculation": true}]}
  1. A calculation named with as.
max([net paid]@m) as Peak
{"projections": [], "calculations": [{"alias": "Peak", "sql": "max([net paid]@m)", "calculation": true}]}
  1. Contribution, running sum, moving average.
pct(web net paid), running_sum(web net paid), moving_avg(web net paid, 7)
{"projections": [
  {"field": "web net paid", "decorators": [{"type": "contribute"}]},
  {"field": "web net paid", "decorators": [{"type": "window", "mode": "running", "function": "sum"}]},
  {"field": "web net paid", "decorators": [{"type": "window", "mode": "moving", "function": "avg", "size": 7}]}]}
  1. Escaped fields: brackets for a name that would otherwise be read as something else, quotes for a field whose name contains an operator word.
[Tables], "Sales in Store" > 5
{"projections": [{"field": "Tables"}], "filters": [{"field": "Sales in Store", "predicate": "greater_than", "value": "5"}]}
  1. Negations.
category != Books, net paid >= 100, name not starts with x
{"projections": [], "filters": [
  {"field": "category", "predicate": "does_not_equal", "value": "Books"},
  {"field": "net paid", "predicate": "greater_than_or_equal_to", "value": "100"},
  {"field": "name", "predicate": "does_not_start_with", "value": "x"}]}
  1. := for a calculation whose alias contains a symbol.
Margin % := ([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)
{"projections": [], "calculations": [{"alias": "Margin %", "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)", "calculation": true}]}
  1. Extract parts.
dom(date), month_name(date) as Mon
{"projections": [
  {"field": "date", "decorators": [{"type": "extract", "extract": "day_of_month"}]},
  {"field": "date", "alias": "Mon", "decorators": [{"type": "extract", "extract": "month_name"}]}]}

The quoted-list gotcha

The shorthand strips the quotes of list items, so a quoted item that contains a comma is later split by the planner:

category in(Books,"Music, Live")
{"field": "category", "predicate": "in_list", "value": "Books, Music, Live"}

That is three values, not two. For list items containing commas, send a JSON spec and quote the item inside the value string (see lists).

Sending an expr

curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_..." \
  -H "Content-Type: application/json" \
  -d '{"expr": "month(date), category, web net paid, category = Books"}'
zsql sql --expr 'month(date), category, web net paid, category = Books'
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({ 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 date and filter category equals Books, so a client can show the user how the line was read before running the SQL.

Next steps