9.5 Combining collections

lookup

Alias: join

Definition. For each row, runs a sub-pipeline over another source and stores its rows as an array field.

Syntax

lookup <source> [as <alias>]
| <read-only stages>
| into <field>
| end

lookup <source> [as <alias>] on <joined field> == <outer field> into <field>     -- one-line equality

source := <collection> | view <name> [version <n>] | values [{…}, …]

Behavior

• into <field> is required, exactly once. The field holds an array (empty when nothing matches).
• The sub-pipeline accepts read-only stages; on, from, with and chunk are refused inside it.
• Field scope. Inside the sub-pipeline, <lookup alias>.f reads the joined row, <outer alias>.f the row being enriched (the source alias, or the alias of the enclosing lookup/union), and unqualified names the joined row. Any expression may mix both.
• Row bounds work inside (| first | attaches only the first match), and so do blocking stages (sort | first, group, distinct), run on the rows joined to each outer row. Stages may follow the end.
• The one-line form expands to lookup <c> | where exists(…) and exists(…) and <joined> == <outer> | into <field> | end: rows where either field is missing do not match. Its left side reads the joined collection, its right side the outer row.
• lookup view <name> needs a view whose source is a bare on <collection> or on values […]; as <alias> names the view’s source, which must not have one already.

Execution

• When the sub-pipeline’s first where begins (after exists guards) with joined == outer, or starts_with(outer, joined), the joined source is indexed once on the joined field. An equality probes one key; a prefix probes each prefix length present (longest-prefix rules read only matching rules).
• Several leading equalities make one composite key; a side read with ? keys a missing field as null (where s.entity == t.entity and s.version? == t.version?).
• When the sub-pipeline reads only known fields of the outer row and is deterministic (no sample, no now()), its result is kept per value of those fields: outer rows sharing them share one run.
• The index and kept results live within the query working set; past it, rows are joined by scan.

Examples

on orders | lookup products on code == product into product
on users as u | lookup orders as o on o.user == u.name into orders

on users as u
| where age > 30
| lookup orders as o
    | where o.user == u.name and o.price * o.qty > 20
    | select order_id, price
    | into big_orders
| end

{"age":31,"big_orders":[{"order_id":2,"price":99.5}],"name":"Alice",…}
{"age":45,"big_orders":[{"order_id":4,"price":250.0},{"order_id":6,"price":10.0}],"name":"Carol",…}

-- Longest-prefix classification
on ledger as a
| lookup rules as r
    | where starts_with(a.account, r.prefix)
    | derive length = len(prefix) | sort length desc | first
    | select category
    | into rule
| end
| derive category = get(at(rule, 0), "category", "unclassified")

-- The single match as a value
on sales as s
| lookup prices as p on p.article == s.article into p
| derive cost = if(len(p) == 1, get(at(p, 0), "cost", null), null)
| drop p

-- Join a view, or literal rows
on orders | lookup view catalog.products as c on c.code == product into product
on ledger | lookup values [{code: "601", label: "Purchases"}] as v on code == account into hit

See also union, at, get, unwind.

union

Definition. Emits the main stream, then the rows of another pipeline.

Syntax

union
| on <source> [as <alias>]
| <read stages>
| end

Behavior

• The first sub-stage must be on <source> (a collection, view <name>, or values […]). from is refused there.
• A branch takes any read pipeline (sort, group, window, fill, lookup, another union…); it then runs as a query of its own, governed like the main one.
• Rows of both sides keep their own fields: align them with select when needed.
• Stages may follow the end; a filter after it is also copied into the branch by the planner.

Examples

on users | select name | union | on products | select label | end

{"name":"Alice"}
{"name":"Bob"}
{"name":"Carol"}
{"name":"Dan"}
{"label":"Pen"}
{"label":"Printer"}
{"label":"Phone"}

on actual | group | by month | sum amount as a | end
| union | on forecast | group | by month | sum amount as a | end | end
| group | by month | sum a as a | end

on view sales.eu | union | on view sales.us | end | union | on view sales.asia | end

See also lookup, views.