PostgreSQL unnest() Function

The PostgreSQL unnest() function expands the specified array into a set of rows.

`unnest()` Syntax

Here is the syntax of the PostgreSQL unnest() function:

unnest(array) -> SETOF

unnest(array, array[, array ...]) -> SETOF, SETOF [, SETOF ...]

Parameters

array: Required. The array to expand.

Return value

The PostgreSQL unnest() function returns a set, and each element in the array becomes a row in the set.

`unnest()` Examples

One-dimensional Array

This example shows how to use the PostgreSQL unnest() function to expand a one-dimensional array into a set.

SELECT unnest(ARRAY[0, 1, 2]);

 unnest
--------
      0
      1
      2

Multidimensional Array

This example shows how to use the PostgreSQL unnest() function to expand a two-dimensional array into a set.

SELECT unnest('[2:4][2:3]={{1,2},{3,4},{5,6}}'::integer[]);

 unnest
--------
      1
      2
      3
      4
      5
      6

All elements of the array becomes a row in the result set.

Expand multiple arrays

SELECT * FROM
  unnest(
    ARRAY[1, 2, 3, 4],
    ARRAY['a', 'b', 'c'],
    ARRAY['A', 'B', 'C', 'D']
  ) AS x(x,y,z);

 x | y | z 
---+---+---
 1 | a | A
 2 | b | B
 3 | c | C
 4 |   | D

Here, the unnest() function expands multiple arrays, which is equivalent to expanding each array into a set individually, and then joins multiple sets by row.

unnest() Syntax