UNNEST
Expands an array into a set of rows, one row per element.
PostgreSQL 17.5
UNNEST(array[, ...])Parameters
- arrayanyarray
- Array; if several arrays are passed, they are expanded into adjacent columns
Return value
A set of rows with the array elements of the same type
Examples
Array into rows
PostgreSQL 17.5
SELECT UNNEST(ARRAY ['sql', 'python', 'java']) AS language;Elements with numbers
PostgreSQL 17.5
SELECT letter,
position
FROM UNNEST(ARRAY ['a', 'b', 'c']) WITH ORDINALITY AS t(letter, position);Two arrays side by side
PostgreSQL 17.5
SELECT *
FROM UNNEST(ARRAY ['a', 'b', 'c'], ARRAY [1, 2]) AS t(letter, num);Details
WITH ORDINALITY adds the element number. If the arrays have different lengths, the missing values are filled with NULL. UNNEST is the reverse of ARRAY_AGG; an empty array or NULL gives an empty set.