Querying from the CLI

Every query command sends a spec and an optional security context to the deployed branch and prints the service’s answer. Write the query as a one-line shorthand (--expr) or as a JSON file (--spec). The CLI parses the shorthand locally into a spec, so what the service receives is always a spec.

The shorthand in one minute

A line of comma-separated items. Each is read from its shape:

item category, month(date), store net paid desc, item category in (Books, Music)
yoy(store net paid), date between 2024-01-01 and 2024-12-31
Share = sum([Store Net Paid]@m) / sum([Catalog Net Paid]@m)

• A filter has a predicate: =, !=, >, >=, <, <=, in (...), not in (...), between a and b, contains, starts with, ends with (each with not), is null, is not null, top N [by measure].
• A calculation is a formula holding a bracket reference ([Name]@m, [Name]@d, [Alias]), named with Alias = formula or formula as Alias.
• Anything else is a projection: a field name or uid, optionally wrapped in decorator functions (month(...), yoy(...), pct(...), running_sum(...), moving_avg(..., 7)), with an optional as Alias and asc/desc.
• [Name] or "Name" is always a field, whatever the name says. Names match without regard to case; add @d or @m when a name is both a dimension and a measure.

The full grammar is on Shorthand; the spec it produces is on Query spec.

Security context

A branch with policies in security.yml refuses to plan without a context (ContextRequired: this branch has security policies; a context is required to plan). Put the context in a file and pass --context:

{"email": "ana@example.com", "groups": [{"name": "Men", "tags": ["cat:Men"]}]}

The shape is on Security context. The transcripts below use the TPC-DS example, which has one policy, so they all pass --context user.json.

zsql sql

zsql sql (--expr LINE | --spec FILE) [--context FILE] [--explain] [--json]

$ zsql sql --expr "item category, store net paid" --context user.json
projection   item category
projection   store net paid
-- datasource: TPC-DS (Postgres)
SELECT
	T1."i_category" AS "Item Category",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
GROUP BY
	T1."i_category"

The projection / filter / calculation lines say how each shorthand item was read; they go to stderr, so zsql sql ... > query.sql captures only the datasource comment and the SQL.

With a spec file (a top-level spec key is unwrapped if present):

{
  "projections": [
    {"field": "Item Category"},
    {"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}]},
    {"field": "Store Net Paid", "order_by": "desc"}
  ],
  "filters": [{"field": "Item Category", "predicate": "in_list", "value": "Books, Music"}]
}

$ zsql sql --spec monthly.json --context user.json
-- datasource: TPC-DS (Postgres)
SELECT
	T2."i_category" AS "Item Category",
	DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN date_dim T1
		ON T0.ss_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ss_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('books', 'music')
GROUP BY
	T2."i_category",
	DATE_TRUNC('month', T1."d_date")::DATE
ORDER BY
	sum(T0."ss_net_paid") desc

Fuzzy corrections

A field name that is close but not exact is corrected, and the correction is printed before the SQL (to stderr):

$ zsql sql --expr "departmnt, store net paid" --context user.json
departmnt → Department (~0.8)
-- datasource: TPC-DS (Postgres)
SELECT ...

The format is term → Field Name (~score). Exact and synonym matches print nothing.

—json

--json prints the service’s response unchanged: sql, datasource, datasource_uid, adapter, corrections when any, and for shorthand queries the spec the CLI sent.

$ zsql sql --expr "year, store quantity" --context user.json --json
{
  "sql": "SELECT\n\tT1.\"d_year\" AS \"Year\",\n\tsum(T0.\"ss_quantity\") AS \"Store Quantity\"\nFROM\n\tstore_sales T0\n\tJOIN date_dim T1\n\t\tON T0.ss_sold_date_sk = T1.d_date_sk\nGROUP BY\n\tT1.\"d_year\"",
  "datasource": "TPC-DS (Postgres)",
  "datasource_uid": "tpcds",
  "adapter": "postgres"
}

zsql explain

zsql explain (--expr LINE | --spec FILE) [--context FILE] [--json]

The SQL, then the plan: the node tree the planner built and where the microseconds went. zsql sql --explain is the same.

$ zsql explain --expr "item category, store net paid, catalog net paid" --context user.json
projection   item category
projection   store net paid
projection   catalog net paid
-- datasource: TPC-DS (Postgres)
WITH ag... AS (...), ag... AS (...)
SELECT ... FULL OUTER JOIN ...

-- 3 nodes
merge final (root) join full
    projections: Item Category, Store Net Paid, Catalog Net Paid
  aggregation ag... on store_sales (tpcds) group by
      paths: store-sales -> item
      projections: Item Category, Store Net Paid
      security: Item Category in (men)
  aggregation ag... on catalog_sales (tpcds) group by
      paths: catalog-sales -> item
      projections: Item Category, Catalog Net Paid

-- server 214 us: parser 11 us, plan 203 us
-- phases (us): ...

Each node is kind alias on table (datasource), with group by, the join type, [segment] and a transform or purpose when present, then indented lists of paths, projections, filters, security filters and masks. The SQL is abbreviated above; each ag... is one aggregation CTE. Two facts sharing Item Category become two aggregation nodes under one merge. --json returns the raw nodes, timings and phases. See Explain for the node fields.

zsql explore

zsql explore (--expr LINE | --spec FILE) [--context FILE] [-q TERM] [--json]

Given a query, what can it take on without breaking: the dimensions it can drill into and the measures it can add. -q narrows by name, uid or synonym, exact containing matches first, then close ones with a score.

$ zsql explore --expr "item category, store net paid" --context user.json -q date
date                                     dim Date                             date
year                                     dim Year                             date
month-of-year                            dim Month of Year                    date
date-id                                  dim Date ID                          date
-- can add 4 dimensions, 0 measures · server 96 us

Without -q, every reachable dimension and measure is listed. Hidden fields are excluded. The last column names the tables a field lives on.

zsql spec

zsql spec LINE...

Local. Prints the spec JSON a shorthand line makes, with the item descriptions, and calls no server. Use it to learn the spec shape or to generate a file for --spec.

$ zsql spec "item category, store net paid desc, item category in (Books, Music)"
projection   item category
projection   store net paid desc
filter       item category in_list Books, Music
{
  "projections": [
    {"field": "item category"},
    {"field": "store net paid", "order_by": "desc"}
  ],
  "filters": [
    {"field": "item category", "predicate": "in_list", "value": "Books, Music"}
  ]
}

zsql fields and zsql tables

zsql fields [Q] [--hidden]
zsql tables

$ zsql fields net
store-net-paid                           msr Store Net Paid
store-net-profit                         msr Store Net Profit
store-net-including-returns              msr Store Net Including Returns
catalog-net-paid                         msr Catalog Net Paid
$ zsql fields catgory
item-category                            dim Item Category ~0.86
$ zsql fields --hidden | grep hidden
store-ticket-number                      dim Store Ticket number (hidden)

Each line is uid dim|msr name, with (hidden) and a ~score for fuzzy matches. Without Q every field is listed; hidden ones only with --hidden.

$ zsql tables
date                             date_dim                         tpcds cost 10
item                             item                             tpcds cost 10
store-sales                      store_sales                      tpcds cost 100
catalog-sales                    catalog_sales                    tpcds cost 100
inventory                        inventory                        tpcds cost 100

uid physical_name datasource cost N. These are the two discovery routes, GET .../fields and GET .../tables; see Discovery.

The repl

zsql repl [--context FILE]

zsql with no subcommand inside a project also starts it. The prompt is zsql> . A plain line is shorthand; a line starting with { is a spec; both go to the sql route and print the SQL with the round trip time. The context given at start applies to every query.

$ zsql repl --context user.json
tpcds/main on https://app.0sql.io: 6 tables, 42 fields, 5 joins, 1 policies
type a line of projections and filters; .help for the forms, .quit to leave
zsql> item category, store net paid, item category in (Books, Music)
projection   item category
projection   store net paid
filter       item category in_list Books, Music
-- datasource: TPC-DS (Postgres)
SELECT
	T1."i_category" AS "Item Category",
	sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('books', 'music')
GROUP BY
	T1."i_category"
-- round trip 38.41 ms
zsql> .explore item category, store net paid ? quantity
store-quantity                           msr Store Quantity                   store-sales
catalog-quantity                         msr Catalog Quantity                 catalog-sales
inventory-quantity-on-hand               msr Inventory Quantity On Hand       inventory
-- can add 0 dimensions, 3 measures · server 102 us
zsql> .branch feature/returns
tpcds/feature/returns on https://app.0sql.io: 7 tables, 48 fields, 6 joins, 1 policies
zsql> .quit

Dot-commands, as in sqlite:

help, quit and the like also work without the dot unless a field has that name. Errors from the service print as error: <class>: <message> and the session continues. History is kept in ~/.zsql/history across sessions.

Next steps

• Shorthand grammar
• Query spec
• Security context
• Query errors