Reference

SQL List Functions

A LIST holds any number of values of one type in a single column. Build one with list() or brackets, read it by position, reshape it with lambdas, and turn it back into rows with unnest.

A list is an ordered set of values of one type, stored in one column: [1, 2, 3], or ['Snake Plant', 'ZZ Plant']. The list() aggregate collects a group's values into one, and unnest turns a list back into rows. In between, every list function works on the whole list at once.

The two priciest products in each category

select c.category_name,
       list(p.product_name order by p.price desc)[1:2] as top_two,
       len(list(p.product_name))                      as products
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
order by c.category_name

Houseplants returns ['Fiddle-Leaf Fig', 'Monstera Deliciosa'] and 5. The order by inside list() sets the order of the items; without it, the order is whatever the rows arrived in.

Practice

Practice building lists with list() and list_contains Practice turning a list back into rows with unnest

Building a list

Form	Gives
`[1, 2, 3]`	A list literal. Every item must convert to one type
`list(x order by y)`	An aggregate: the group's values, in order. `array_agg` is the same function
`list(distinct x)`	Each value once
`string_split(s, ',')`	A list of text from a delimited string
`range(1, 4)`	`[1, 2, 3]`: the end is left out
`a \|\| b`, `list_concat(a, b)`	Two lists joined end to end

Reading a list

Form	Gives
`l[1]`, `l[-1]`	The first and last item. Positions start at 1; 0 and positions past the end return `NULL`
`l[2:3]`, `l[:2]`, `l[3:]`	A slice, including both ends
`len(l)`	The number of items. `len([])` is 0
`list_contains(l, x)`	True if `x` is an item
`list_position(l, x)`	Where `x` first appears, or `NULL`
`list_has_any(a, b)`, `list_has_all(a, b)`	True if the lists share any item, or if `a` holds every item of `b`

Index, slice, search

select ['/', '/blog', '/checkout', '/signup'] as journey,
       journey[1]                         as first_page,
       journey[-1]                        as last_page,
       journey[2:3]                       as middle,
       list_contains(journey, '/pricing') as saw_pricing,
       list_position(journey, '/checkout') as checkout_step

Working on every item

Function	Gives
`list_sort(l)`, `list_reverse(l)`	The items sorted, or reversed
`list_distinct(l)`	Each value once, NULLs dropped, in no promised order. Sort it if order matters
`list_sum`, `list_avg`, `list_min`, `list_max`	An aggregate applied to the items
`list_transform(l, lambda x: ...)`	Each item replaced by the expression
`list_filter(l, lambda x: ...)`	The items for which the expression is true
`[x * 2 for x in l if x > 1]`	Both at once, as a list comprehension
`flatten(l)`	A list of lists joined into one list

Lambdas over Garden Shop prices

select category_id,
       list(price order by price)                       as prices,
       list_transform(prices, lambda p: round(p * 1.1, 2)) as raised,
       list_filter(prices, lambda p: p >= 15)           as over_15,
       [p for p in prices if p < 10]                    as under_10
from products
where category_id in (1, 3)
group by category_id
order by category_id

A lambda names each item (p here) and gives the expression to apply. lambda p: ... is the form DuckDB's documentation now uses. Older code writes p -> ..., which still runs, and that arrow is why DuckDB's JSON -> operator behaves oddly next to = (see the JSON reference).

Back to rows with unnest

unnest(l) in a SELECT returns one row per item, and copies the row's other columns onto each. A row whose list is empty or NULL produces no rows at all. To keep it, unnest in the FROM clause with a left join:

Keep the row with an empty list

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

Gift Card comes back once, with a NULL product. Two unnest calls in one SELECT run side by side, not as every combination: the shorter list is padded with NULL. unnest(l, recursive := true) also unnests lists inside the list, and turns structs into columns.

What can go wrong

list() keeps NULLs. Most aggregates skip them; list adds a NULL item for each, so len(list(x)) counts rows, not values. Use list(x) filter (where x is not null) to leave them out.
A group with no rows gives NULL, not []. list(x) filter (where false) is NULL, and len of it is NULL too. Wrap it in coalesce(..., []) when an empty list is what you mean.
list_unique is a count. It returns how many distinct values the list has (list_unique([1, 1, 2]) is 2). list_distinct returns the list.
Mixed types fail. [1, 'a'] stops with Could not convert string 'a' to INT32: the list takes its type from the items, and 'a' can't become a number.

A list of mixed types

select [1, 'a'] as mixed

How other databases differ

Engine	Type and aggregate	First item	To rows
DuckDB	`LIST`, `list()` / `array_agg`	`l[1]`	`unnest(l)`
PostgreSQL	Arrays, `array_agg`	`a[1]`	`unnest(a)`; search with `x = any(a)`
BigQuery	`ARRAY`, `ARRAY_AGG`	`a[OFFSET(0)]` or `a[ORDINAL(1)]`: zero-based by default	`CROSS JOIN UNNEST(a)`
Snowflake	`ARRAY`, `ARRAY_AGG`	`a[0]`: zero-based	`LATERAL FLATTEN(input => a)`
MySQL, SQL Server, SQLite	No list type; a JSON array or delimited text stands in	A JSON path such as `'$[0]'`	`JSON_TABLE`, `OPENJSON`, or `json_each`

Dialect note

DuckDB's lists count from 1, like PostgreSQL's arrays, but JSON arrays count from 0 everywhere, DuckDB included: payload->'items'->>0 is the first item. Moving between the two in one query is the easiest place to be off by one.

Lesson

SQL List Practice: list(), Indexing, and list_contains

Collect each session's pages into one ordered list.

Lesson

SQL UNNEST Practice: Turn a List Into Rows

Unnest gift bundles and price them against products.

Lesson

SQL Struct Practice: Named Fields in One Value

Keep each customer's rental history as a list of structs.

Reference

SQL Structs and Maps

Named fields in one value, and key-value maps.