SQLearnAcademy
←Function reference
Arrays and seriesSQL standard

UNNEST

Expands an array into rows — one row per element.

Syntax

UNNEST(<array>)
UNNEST(<array>) WITH ORDINALITY

The opposite of ARRAY_AGG: from a single array value it makes as many rows as the array has elements. Handy when a list is stored in one field, such as tags, but you need to work with it like a table: filter, group, join.

An array into rows
SELECT UNNEST(ARRAY['Emma', 'Liam', 'Olivia']) AS name;
name
Emma
Liam
Olivia
Tags from a string as separate rows
SELECT UNNEST(STRING_TO_ARRAY('sql,postgres,database', ',')) AS tag;
tag
sql
postgres
database
Together with the element numberAdvanced
SELECT tag, position
FROM UNNEST(ARRAY['sql', 'postgres', 'database']) WITH ORDINALITY AS t(tag, position);
tagposition
sql1
postgres2
database3

WITH ORDINALITY adds a column with the element number, and t(tag, position) names the columns.

Watch out

An empty array or NULL gives zero rows. If UNNEST sits in SELECT next to other columns, such a table row disappears from the result completely.

Two UNNEST calls of different lengths in the same SELECT are aligned to the longer one, and the missing elements become NULL.

Without ORDER BY, the order of rows isn't guaranteed. If you need the element number, add WITH ORDINALITY.