Unnesting Lists into Rows

`UNNEST` turns each element of a list column into its own row. This is essential when working with JSON or other data that stores arrays inside columns.

If the products table has a `tags` column of type `VARCHAR[]`, you can expand it:

SELECT name, unnest(tags) AS tag
FROM products

For a product with two tags, this produces two rows — one per tag. The other columns are repeated for each expanded element.

An empty list produces zero rows for that input, so the parent row disappears from the result. `NULL` lists also produce zero rows. The sample data includes both cases so you can see that they do not appear after unnesting.

DuckDB supports the convenient `SELECT unnest(...)` form. Other databases often use a lateral join or `CROSS JOIN UNNEST` syntax instead.