UNNEST
UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape.
Overview
UNNEST is the standard SQL mechanism for flattening nested collection types — lists/arrays and, in engines that support it, structs — into rows. It's essential whenever source data (JSON, Parquet with nested columns, API responses) arrives with one row containing an array of values that you need to analyze one element at a time.
Copy code
SELECT customer_id, UNNEST(tags) AS tag
FROM customers;
If a customer has tags = ['vip', 'newsletter'], this produces two output rows, one per tag, with customer_id repeated.
Unnesting multiple lists together
When multiple list columns are unnested in the same SELECT, most engines (including DuckDB) unnest them side by side, positionally, rather than producing a cross product:
Copy code
SELECT UNNEST([1, 2, 3]) AS a, UNNEST([10, 11]) AS b;
-- (1, 10), (2, 11), (3, NULL) -- shorter list is padded with NULL
DuckDB specifics
DuckDB's UNNEST also flattens STRUCT columns into multiple named columns (one per struct field), and supports a recursive := true argument to fully flatten arbitrarily nested lists-of-structs or lists-of-lists in one call:
Copy code
SELECT UNNEST([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);
A max_depth argument caps how many nesting levels are unnested, useful when you want to flatten only partway into a deeply nested structure. DuckDB also supports the equivalent function-call syntax, unnest(list_column), and pairs naturally with list_value/list literals ([1, 2, 3]) and generate_series for building or exploding array-shaped data.
Related terms
LIST and ARRAY are nested SQL data types for storing an ordered collection of values in a single column — LIST is variable-length, ARRAY is fixed-length.
struct →A struct in DuckDB is a composite data type that allows you to group multiple fields of different types into a single unit.
MAP type →MAP is a nested SQL data type storing an ordered collection of unique key-value pairs, useful for representing dynamic or sparse attributes within a single column.
column →A column represents a single field or attribute in a database table or DataFrame that contains values of the same data type.
UNPIVOT →UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
FAQS
DuckDB (and compatible engines) unnest them side by side positionally rather than producing a cross join; the shorter list is padded with NULL values so both outputs have the same number of rows.
In DuckDB, yes — UNNEST expands a STRUCT column into one output column per field, and with recursive := true it fully flattens combinations of nested lists and structs in a single call.
