2. A guided tour

Twelve short steps through the language. Each one adds one idea.

2.1 Read a collection

on users

Every document of users. from users is the same.

2.2 Keep some rows

on users | where age >= 18 and city == "Paris"

{"name":"Alice","age":31,"city":"Paris",…}
{"name":"Dan","age":28,"city":"Paris",…}

where keeps the rows whose predicate is true. Conditions combine with and, or, not, and parentheses.

| separates stages. A query without | can separate them with ;, or write one stage per line:

on users
where age >= 18
  and city == "Paris"

Both styles read the same (3.2); this book writes |.

2.3 Choose and compute fields

on orders | select order_id, price * qty as total, upper(status) as status

{"order_id":1,"status":"PAID","total":20.0}
…

select keeps only what it lists (plus _id). To add fields and keep the rest, use derive:

on orders | derive total = price * qty, big = total > 100

2.4 Sort and page

on orders | sort price * qty desc | limit 3

sort takes fields or expressions; limit and skip page. A sort followed by limit runs as a bounded Top-N.

2.5 Aggregate

on orders | where status == "paid" | group | by region | sum qty as units | avg price | count as orders | end

{"avg_price":54.75,"orders":2,"region":"EU","units":3}
{"avg_price":130.0,"orders":2,"region":"US","units":5}

The compound group opens a sub-pipeline: one by, then any number of measures, closed by end. The one-line form covers the common cases: group region, qty sums qty per region.

2.6 Join

on orders | lookup products on code == product into product

Each order receives an array product holding the matching products. For anything richer than one equality, write the sub-pipeline:

on users as u
| lookup orders as o
    | where o.user == u.name and o.status == "paid"
    | select order_id, price
    | into paid_orders
| end

2.7 Stack two sources

on actual | select month, amount | union | on budget | select month, amount | end

2.8 Reuse a value

let $since = "2026-02", $min = 50
| on orders
| where month >= $since and price * qty > $min

let names values once per query. $name can also come from the request (params).

2.9 Name a query: a view

define view sales.paid
| description "Paid orders with their total"
| exec
  | on orders | where status == "paid" | derive total = price * qty
| end
| end

Then read it like a collection:

on view sales.paid | group | by region | sum total | end

2.10 Keep it computed: a materialized view

define view sales.by_month | materialized | exec
  | on view sales.paid | group | by month, region | sum total | end
| end
| end

The first read computes the rows and keeps them. Later reads use them, until orders changes, or a view it reads is redefined.

2.11 Write

on users | insert {name: "Eve", age: 22, city: "Lyon"}
on users | where name == "Bob" | set age = age + 1
on orders | where status == "cancelled" | delete
on orders | group | by region | sum qty as units | end | into region_totals

Every write query is one transaction.

2.12 Look under the hood

explain on orders | where qty > 1 | sort price * qty desc | limit 3
on _views | where state == "active" | select name, version, reads
on _memory | where scope == "global"

explain shows the plan without running it; system collections expose views, caches and engine state.

Next: the lexical structure, or jump to the stage reference.