A. Cookbook

Recipes by task. Each is a complete query.

Reading

-- One document by id (primary-key lookup)
on users | where _id == "01900000-0000-7000-8000-000000000003"

-- Distinct values of a field
on orders | select region | distinct

-- Rows lacking a field
on data | where not exists(date)
on data | where date? == null

-- Exactly one row, or fail
on users | where name == "Alice" | single

-- A random sample
on orders | where status == "paid" | sample 100

-- Literal rows as a source
on values [{code: "FR", rate: 0.2}, {code: "DE", rate: 0.19}]

Computing

-- Safe ratio and a label
on orders | derive ratio = div(price, qty, 0), band = if(ratio > 50, "high", "low")

-- First non-empty label
on items | derive label = coalesce(nullif(trim(label?), ""), code)

-- Normalized text
on users | select name, lower(trim(city)) as city

-- Tags
on users | where "admin" in tags or len(tags) == 0

Aggregating

-- Top 3 customers by paid revenue
on orders | where status == "paid" | derive revenue = price * qty
          | group | by user | sum revenue | end | sort revenue desc | limit 3

-- Count per value, most frequent first
on users | where age >= 18 | group city | sort count desc

-- Several measures
on orders | group | by region | sum qty as units | avg price as basket | max qty | count as orders | end

-- Conditional measures
on ledger | group | by account | sum amount where amount > 0 as debit | sum amount where amount < 0 as credit | end

-- Last value by date
on balances | group | by account | last balance by date as closing | end

-- HAVING
on orders | group | by user | sum qty as units | end | where units > 3

-- Grand total
on orders | derive all = "total" | group | by all | sum qty as units | end

-- Distribution
on orders | group | by region | median price | percentile price 0.9 as p90 | stddev price | end

-- Age bands
on users | bucket age by [0, 18, 30, 50, 120] as band

-- A cross table
on orders | pivot | rows region | columns month | values qty | aggregate sum | end

Time

-- Orders per month from an ISO timestamp
on orders | derive month = date_format(created_at, "%Y-%m") | group month

-- Complete missing months with zeros
on monthly | fill month from "2026-01" to "2026-12" step "month" by product with amount = 0

-- Month over month and year to date
on monthly
| window | partition by product | order by month | lag amount as previous | cumsum amount as ytd | end
| derive change = amount - coalesce(previous, 0)

-- Rank within a partition
on sales | window | partition by region | order by amount desc | row_number as rank | end | where rank <= 3

-- Fiscal year closing in June
on ledger | derive fy = fiscal_year(date, 6) | group | by fy | sum amount | end

Joining

-- Attach one related row
on orders | lookup products on code == product into product

-- Attach a filtered set
on users as u | lookup orders as o | where o.user == u.name and o.status == "paid" | select order_id | into paid | end

-- Keep the joined field as a value
on orders as o
| lookup products as p on p.code == o.product into p
| derive label = get(coalesce(at(p, 0), {}), "label", "?")
| drop p

-- Longest-prefix classification
on ledger as a | lookup rules as r | where starts_with(a.account, r.prefix)
  | derive n = len(prefix) | sort n desc | first | into rule | end

-- Anti-join: orders without a known product
on orders | lookup products on code == product into p | where len(p) == 0

-- Stack two sources
on actual | select month, amount | union | on budget | select month, amount | end

Writing

-- Insert one, insert many
on users | insert {name: "Eve", age: 22}
on users | insert [{name: "Gus"}, {name: "Hal"}]

-- Update, increment, flag the first
on users | where name == "Bob" | set age = age + 1
on counters | where name == "visits" | set value = value + 1
on orders | where status == "pending" | sort order_id | limit 1 | set priority = true

-- Delete, purge
on orders | where status == "cancelled" | delete
on staging | delete

-- Idempotent upsert, update-only
on customers | load | with merge | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","tier":"gold"}] | end
on customers | load | with update | chunk [{"_id":"01900000-0000-7000-8000-00000000c001","tier":"platinum"}] | end

-- Save a result
on orders | group | by region | sum qty as units | end | into region_totals

-- Restore yesterday's state
on orders as of "2026-10-20" | into orders_restored

Views

-- A view, then a report over it
define view sales.paid | exec | on orders | where status == "paid" | derive total = price * qty | end | end
on view sales.paid | group | by region | sum total | end

-- A parameter with a default
define view orders.big | exec | let $min ?= 2 | on orders | where qty >= $min | end | end
let $min = 4 | on view orders.big

-- A materialized base and a thin view over it
define view sales.by_month | materialized | exec | on view sales.paid | group | by month, region | sum total | end | end | end
define view sales.window | exec | on view sales.by_month | where month >= $from and month <= $to | end | end

-- Inspect
on _views | where state == "active" | select name, version, reads
on _cache | select view, version, place, computedAt
explain let $from = "2026-01", $to = "2026-06" | on view sales.window

Engine

-- Health
on _memory | where scope == "global" | select pressure_state, current_bytes, peak_bytes, spill_runs_active

-- Slowest shapes
on _index_observations | sort average_elapsed_us desc | limit 10

-- How a query runs
explain on orders | sort price * qty desc | limit 10