PostgreSQL 18 reference · Array Functions

PostgreSQL UNNEST() Function

Expand an array into a set of rows, one per element. With several arrays it zips them into columns.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

unnest(array)  |  unnest(array1, array2, ...)

Parameter	Description
`array`	An array value.

A set of rows.

Array to rows

SELECT unnest(ARRAY['red', 'green', 'blue']) AS color;

Output

 color
-------
 red
 green
 blue
(3 rows)

Run this on your computer — see “Get Started” for setup.

Zip arrays, with positions

SELECT * FROM unnest(ARRAY['tea', 'cake'], ARRAY[3.50, 12.00]) WITH ORDINALITY AS t(item, price, n);

Output

 item | price | n
------+-------+---
 tea  |  3.50 | 1
 cake | 12.00 | 2
(2 rows)

Run this on your computer — see “Get Started” for setup.

ARRAY_AGG() · Collect a group's values, including NULLs, into an array, optionally in a given order.
STRING_TO_ARRAY() · Split a string on a delimiter into a text array. A third argument turns matching fields into NULL.
JSONB_ARRAY_ELEMENTS() · Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.
SQL String Functions · tutorial