9.1 Filtering and shaping

Streaming stages that keep rows, or change each row into exactly one row.

where

Definition. Keeps the rows whose predicate is true.

Syntax

where <predicate>

Behavior

• The predicate is any expression giving a boolean. A row for which it gives false is dropped; any other kind is an error.
• Chained where stages narrow further; where a | where b is where a and b.
• A field the predicate reads must exist on every row reaching it (use field? or exists() otherwise).
• where _id == "<uuid>" runs as a primary-key lookup.
• The planner moves filters toward the source when that changes neither the rows nor the errors (14.2). A filter written after a view reaches the view’s source.

Examples

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

{"age":31,"name":"Alice"}
{"age":28,"name":"Dan"}

on users | where (city == "Paris" or city == "Lyon") and age < 30
on users | where active == false
on users | where profile.score >= 8
on users | where "dev" in tags
on users | where nick? == "Zed"                -- rows without nick are simply not kept
on orders | where status in ["paid", "pending"] and region not in ["US"]
on orders | where price * qty between 20 and 100
on orders | where starts_with(month, "2026-")

Errors and limits

• field path "x" is missing: a row lacks a field read without ?.
• operation "==" is incompatible with values …: a comparison of two kinds (age == "31").

See also exists, coalesce, single.

select

Alias: project

Definition. Keeps only the listed fields and named expressions, plus _id.

Syntax

select <item> [, <item>]*
item := <field.path> [as <field.path>] | <expression> as <field.path>

Behavior

• A field path keeps that field; a nested source is rebuilt nested (select profile.score gives {"profile":{"score":…}}).
• as renames, and may write a nested path (profile.country as geo.country).
• A computed item needs as.
• Items are evaluated left to right: a later item reads an alias introduced earlier (select price * qty as total, total > 50 as big).
• A missing field in a plain field item is skipped, not an error.
• At most one select per pipeline.

Examples

on orders | select order_id, price * qty as total, product as sku | limit 2

{"order_id":1,"sku":"P1","total":20.0}
{"order_id":2,"sku":"P2","total":99.5}

on users  | select name, profile.country as geo.country
on orders | select order_id, "fixed" as label, 42 as answer
on orders | project order_id, (price * qty) - 1 as net

See also derive, drop, rename.

derive

Definition. Adds or replaces computed fields, keeping the rest of the row.

Syntax

derive <field.path> = <expression> [, <field.path> = <expression>]*

Behavior

• Assignments run left to right: each sees the fields computed before it.
• A target path may be nested (meta.total = …) and may overwrite an existing field.
• The list splits only on top-level commas, so arrays and multi-argument calls are safe.
• It never changes stored data: use set to write.

Examples

on orders | derive total = price * qty, big = total > 100, label = if(big, "big", "small")
          | select order_id, total, label | limit 2

{"label":"small","order_id":1,"total":20.0}
{"label":"small","order_id":2,"total":99.5}

on orders | derive r = qty % 2, n = -qty
on users  | derive inactive = not active
on orders | derive flag = true, pair = [qty, qty * 2], obj = {"a": 1}, meta.total = price * qty
on sales  | derive month = date_format(date, "%Y-%m"), year = date_part(date, "year")

See also select, set, functions.

rename

Definition. Moves one field to another path.

Syntax

rename <source.path> as <target.path>

Behavior. The value moves; the source path disappears. A row without the source passes unchanged. Source and target must differ.

Examples

on users | rename name as full_name | select full_name, age
on users | rename profile.score as rating

See also select … as.

drop

Definition. Removes fields from the rows.

Syntax

drop <field.path> [, <field.path>]*

Behavior. Missing fields are ignored. Stored data is not changed.

Example

on users | drop tags, profile

{"active":true,"age":31,"city":"Paris","name":"Alice"}

See also select.

root

Definition. Replaces each row by the object stored at a path.

Syntax

root <field.path>

Example

on users | where name == "Alice" | root profile

{"country":"FR","score":9.5}

Pattern. After a lookup … | first | into hit, unwind hit | root hit turns the joined row into the row.

See also unwind, get.