9.3 Arrays, deduplication and counting

unwind

Definition. Emits one row per item of an array field, the item in place of the array.

Syntax

unwind <field.path> [with index <field.path>] [keep empty]

Behavior

• Items come out in order.
• A row whose field is missing or not an array passes through unchanged.
• A row whose array is empty is dropped, unless keep empty: it is kept with the field set to null.
• with index <field> stores the 0-based position of each item (null on rows not expanded).
• Streams mid-pipeline; works inside lookup sub-pipelines.

Examples

on users | unwind tags with index position | select name, tags, position

{"name":"Alice","position":0,"tags":"admin"}
{"name":"Alice","position":1,"tags":"dev"}
{"name":"Bob","position":0,"tags":"dev"}
{"name":"Dan","position":0,"tags":"ops"}

on users | unwind tags keep empty | where tags == null | select name     -- {"name":"Carol"}
on values [{m: 0}] | derive m = range(0, 12) | unwind m                  -- a row per month index
on users | lookup orders on user == name into o | unwind o | root o      -- the joined rows as rows

See also range, list measure.

distinct

Definition. Keeps one row per distinct value.

Syntax

distinct                               -- whole-row equality
distinct <field.path> [, <field.path>]*  -- the first row of each key

Behavior. Without fields, two rows are equal when every field is (_id aside, after a select). With fields, the first row of each key is kept whole. Keys compare as sort compares them. Blocking; after another blocking stage it keeps only the keys it has seen and emits each first row as it comes.

Examples

on orders | select status | distinct

{"status":"paid"}
{"status":"pending"}
{"status":"cancelled"}

on orders | distinct region, status
on orders | distinct product | sort product

count

Definition. Returns one row holding the number of input rows.

Syntax

count
count as <alias>

Behavior. Terminal: nothing follows it. The default field is count. Streaming: it counts without keeping rows. To count per value, use group <key>; to count a value inside a group, count <field> or count where <predicate>.

Examples

on orders | where price > 20 | count as expensive_orders
-- {"expensive_orders":3}
on orders | group status            -- a count per status

Errors. … follows a terminal operator when a stage follows it. To go on after counting, use the compound group with a constant key: derive k = 0 | group | by k | count as n | end | ….