TopolearnLine items, tags and nested events must become rows before you can join or aggregate them.
FLATTEN is a table function that turns each element of an array (or each key of an object) into a row. You combine it with the source table through LATERAL, or the equivalent , TABLE(FLATTEN(...)) form, so each row is joined to its own elements.
Each output row has columns you refer to through the alias:
VALUE: the element itself (a VARIANT, so cast it);INDEX: the position in an array,KEY: the key for objects;PATH,SEQ(identifies the source row) andTHIS(the element being flattened).
Options:
OUTER => TRUEemits one row with NULLs for empty or missing arrays. Without it, source rows whose array is empty disappear, just like an inner join.RECURSIVE => TRUEwalks every nested level, which is useful for discovering a document's keys.PATH => 'a.b'flattens a nested path.
To flatten two levels, such as orders → items → discounts, chain two LATERAL FLATTENs, the second taking f1.value:discounts. Going the other direction, ARRAY_AGG(... ) WITHIN GROUP (ORDER BY ...) re-nests rows into an array.
A common pattern is a view or dbt model that flattens raw VARIANT events into typed, relational columns once, so analysts query normal tables.
Example
-- raw_events.src = {"id": 7, "items": [{"sku":"A1","qty":2},{"sku":"B9","qty":1}], ...}
SELECT e.src:id::INT AS order_id,
i.index AS line_no,
i.value:sku::STRING AS sku,
i.value:qty::INT AS qty
FROM raw_events e,
LATERAL FLATTEN(input => e.src:items) i;
-- 7 | 0 | A1 | 2
-- 7 | 1 | B9 | 1
-- Keep orders with no items
SELECT e.src:id::INT AS order_id, i.value:sku::STRING AS sku
FROM raw_events e,
LATERAL FLATTEN(input => e.src:items, OUTER => TRUE) i;
-- Discover every key path in the documents
SELECT DISTINCT f.path
FROM raw_events e, LATERAL FLATTEN(input => e.src, RECURSIVE => TRUE) f;Remember
- LATERAL FLATTEN turns array elements into rows: use value, index, key
- Without OUTER => TRUE, rows with empty arrays vanish
- RECURSIVE => TRUE lists every nested path
Watch out for
- Counting orders after flattening without OUTER and silently losing orders with no items.