Unnesting Lists in SQL

Lists also arrive from outside: an export with a column of tags, an API that sends a list of product ids, a Parquet file with an array column. You can't join a list to a table directly. unnest turns it back into rows first:

select unnest(['/', '/blog', '/checkout']) as path

One list in, three rows out, one per item.

The other columns repeat

Put unnest in a SELECT beside ordinary columns and each row becomes as many rows as its list has items, with the ordinary columns copied onto each one. Garden Shop is planning gift bundles, each a list of product ids:

with bundles(bundle_name, product_ids) as (
  values ('Starter Kit', [3, 10, 12, 15]),
         ('Patio Glow',  [21, 22, 24])
)
select bundle_name,
       unnest(product_ids) as product_id
from bundles

Two bundles become seven rows: four for Starter Kit and three for Patio Glow. That's a table of bundle and product pairs, ready to join to products.

An empty list makes no rows

A row whose list is empty, or NULL, has nothing to unnest, so it disappears, the way an inner join drops a row with no match:

with bundles(bundle_name, product_ids) as (
  values ('Patio Glow', [21, 22, 24]),
         ('Gift Card',  []::integer[])
)
select bundle_name,
       unnest(product_ids) as product_id
from bundles

Only Patio Glow's three rows come back. To keep every bundle, unnest in the FROM clause and left join to it. Each row joins to its own list's items, and a row with none keeps a single NULL:

with bundles(bundle_name, product_ids) as (
  values ('Patio Glow', [21, 22, 24]),
         ('Gift Card',  []::integer[])
)
select b.bundle_name,
       u.product_id
from bundles as b
left join unnest(b.product_ids) as u(product_id) on true
order by b.bundle_name, u.product_id

Lists stored as text

Often the list isn't a list yet but a string like '3, 10, 12, 15'. string_split turns it into a list of text, and a cast turns each piece into a number:

with imported(bundle_name, product_ids) as (
  values ('Starter Kit', '3, 10, 12, 15')
)
select bundle_name,
       unnest(string_split(product_ids, ','))::integer as product_id
from imported

The pieces keep their spaces (' 10'), which DuckDB's cast to an integer ignores. Before comparing the pieces as text, wrap them in trim.

In other databases

PostgreSQL also has unnest, and its string_to_array does what string_split does here. BigQuery writes CROSS JOIN UNNEST(list), Snowflake uses LATERAL FLATTEN, and SQL Server splits text with CROSS APPLY STRING_SPLIT.