DuckDB SQL UNNEST Explained: Lists, Structs, Recursive Flattening, and max_depth

CSV data is usually flat. JSON and Parquet data often contain lists and structs. UNNEST helps turn that nested shape into rows and columns you can filter, join, and aggregate.

SQL for Files runs DuckDB in the browser, making it a useful place to practice UNNEST on local nested files without uploading them.

When a column holds a list, UNNEST turns each list element into its own row. The other selected columns are repeated for each element.

SELECT name, unnest(tags) AS tag
FROM products
ORDER BY name, tag;

A struct is different from a list. Unnesting a struct expands its fields horizontally into separate columns while keeping the row count the same.

SELECT name, unnest(address)
FROM contacts
ORDER BY name;

recursive := true flattens nested lists all the way down. max_depth lets you peel off only a limited number of layers when you want to preserve part of the nested structure.

SELECT label, unnest(grid, recursive := true) AS val
FROM matrices
ORDER BY label, val;

1. Unnest list columns into one row per element.
2. Unnest struct columns into separate fields.
3. Use recursive unnesting for deeply nested lists.
4. Use max_depth to control how much nesting is removed.