C. Quick reference
Query
[explain] [let $a = …, $b ?= … |] on <collection | values […] | view <name>> [as of … | history] [as <alias>] | <stage> | …
Separators
on orders | where qty > 1 | sort qty desc -- `|`
on orders; where qty > 1; sort qty desc -- `;`, in a query without `|`
on orders -- line breaks, in a query without `|`
where qty > 1
sort qty desc
One style per query. A line starting with a stage word (or a directive of its block) starts a stage, unless the
line before ends with ,, an operator or a word such as and, as, on. end closes a block. Text in
parentheses keeps its own style. Details: 3.2. Below, | stands for any
separator.
Stages
| Stage | Form |
|---|---|
where | where <predicate> |
select / project | select f, expr as name, … |
derive | derive f = expr, … |
rename | rename a as b |
drop | drop f, … |
root | root f |
near | near vec, [x, y, …] |
sort | sort key [asc|desc], … |
limit | limit n |
skip / offset | skip n |
first | first [f] |
single | single [f] |
sample | sample n |
unwind | unwind f [with index i] [keep empty] |
distinct | distinct [f, …] |
count | count [as n] |
group | group k, v · group | by k | sum v | avg v | min v | max v | first v [by o] | last v [by o] | count [v] | list v | median v | percentile v f | stddev v [where p] [as n] | end |
bucket | bucket expr by [b0, b1, …] [default v] [as n] |
pivot | pivot | rows r | columns c | values v | aggregate sum | end |
window | window | partition by p | order by o | lag f [n] | lead f [n] | cumsum f | first f | last f | row_number [as n] | end |
fill | fill f from a to b step s [by p, …] [with g = v, …] |
lookup / join | lookup c on a == b into f · lookup c as x | … | into f | end |
union | union | on c | … | end |
insert | insert {…} · insert [{…}, …] |
set | set f = expr, … |
delete | delete |
into / out | into c |
load | load | with merge|replace|update | chunk […] | end |
Views
define view <name> [| description "…"] [| materialized] | exec | <query> | end | end
define view <name> | version <n> | end
undefine view <name>
on view <name> [version <n>]
Operators (lowest to highest)
or || · and && · not ! · == != · < <= > >= in not-in between · + - · * / % · unary - +
Functions
| Category | Functions |
|---|---|
| Numeric | abs sqrt pow round trunc floor ceil sign ln log10 log exp min max sum avg median percentile stddev div |
| Logic | if case coalesce exists optional nullif between bucket |
| Conversion | to_int to_float to_string to_bool type |
| Text | len lower upper trim ltrim rtrim concat contains starts_with ends_with substr split replace index_of pad_left pad_right |
| Array | at slice reverse sort_array unique flatten range |
| Object | keys values merge get has |
| Date | now parse_date date_trunc date_add date_diff date_part date_format month_end days_in |
| Fiscal | fiscal_year fiscal_month fiscal_start fiscal_end |
Missing fields
f? · exists(f) · coalesce(f, d) · optional(f) · if(exists(f), f, d)
Date units and parts
Units: year quarter month week day hour minute second millisecond (plural accepted).
Parts: year quarter month week day day_of_week day_of_year hour minute second millisecond.
Format: %Y %m %d %H %M %S %L %j %u %V %F %T %%.