Skip to main content

UNNEST

Lift struct or array of struct fields into the parent or enclosing result set.

Syntax​

UNNEST expression

Parameters​

  • expression - An expression that evaluates to a struct or array of struct

Description​

The UNNEST command lifts struct or array of struct fields into the parent or enclosing result set. When given a struct, it lifts struct fields into the parent struct without changing row cardinality. When given an array of struct, it performs an explode operation followed by unnesting, creating one row per array element with the struct fields lifted into the parent struct.

When the expression is a simple column reference, the source column is consumed and removed from the output. This avoids duplicating the (potentially large) source array or struct across every output row.

-- Before UNNEST: schema is {row: int, stuff: array({a: int, b: int})}
FROM tbl | UNNEST stuff
-- After UNNEST: schema is {a: int, b: int, row: int}
-- "stuff" is no longer in the output
  • NEST - Nests all pipeline rows into a sub-structure (inverse operation)