Learn SQL Lesson

Recursive UNNEST for Nested Data

When lists contain other lists, a single `UNNEST` only peels off one layer. Use the `recursive` parameter to flatten all levels at once.

SELECT unnest([[1, 2], [3, 4, 5]], recursive := true)

Without `recursive` this returns two rows — each inner list as a value. With `recursive := true` it returns five rows — the individual integers.

This works on structs too. A list of structs with recursive unnesting first expands the list into rows, then expands each struct into columns:

SELECT unnest([{'a': 1, 'b': 2}, {'a': 3, 'b': 4}], recursive := true)

Note that lists inside structs are not unnested — recursive unnesting first fully unnests all lists, then fully unnests all structs.

Practice challenge

Recursively unnest the grid column to get individual integers. Return label and the unnested value as val, ordered by label, val.

Open interactive editor