UNNEST

Expands the elements of one or more collection columns (ARRAY or SET) into individual rows.

Expands the elements of one or more collection columns (ARRAY or SET) into individual rows. If called with a single array, UNNEST returns the elements in a column named value. If called with two or more arrays, it returns columns named val_column-name. You can use an AS clause in the SELECT to change these names.

UNNEST and EXPLODE both expand collections. They have the following differences:

  • By default, UNNEST expands all passed collections and EXPLODE expands only the first. See the explode_count and explode_all parameters.

  • By default, UNNEST returns only the elements and EXPLODE also returns their positions in an index column. See the with_offsets parameter.

  • By default, UNNEST does not partition its input and ignores an OVER() clause if present. See the skip_partitioning parameter.

Behavior type

Immutable

Syntax

UNNEST (column[,...])
 [USING PARAMETERS param=value])
[ OVER ( [window-partition-clause

Arguments

column
Column in the table being queried. If explode_all is false, you must specify at least as many collection columns as the value of the explode_count parameter. Columns that are not collections are passed through without modification.

Passthrough columns are not needed if skip_partitioning is true.

OVER(...)
How to partition and sort input data. The input data is the result set that the query returns after it evaluates FROM, WHERE, GROUP BY, and HAVING clauses.

This clause only applies if skip_partitioning is false.

Parameters

explode_all (BOOLEAN)
If true, explode all collection columns. When explode_all is true, passthrough columns are not permitted.

Default: true

explode_count (INT)
The number of collection columns to explode. The function checks each column, up to this value, and either explodes it if is a collection or passes it through if it is not a collection or if this limit has been reached. If the value of explode_count is greater than the number of collection columns specified, the function returns an error.

If explode_all is true, you cannot specify explode_count.

Default: 1

skip_partitioning (BOOLEAN)
Whether to skip partitioning and ignore the OVER clause if present. UNNEST translates a single row of input into multiple rows of output, one per collection element. There is, therefore, usually no benefit to partitioning the input first. Skipping partitioning can help a query avoid an expensive sort or merge operation.

Default: true

with_offset (BOOLEAN)
Whether to return the index of each element as an additional column.

Default: false

Null-handling

This function expands each element in a collection into a row, including null elements. If the input column is NULL or an empty collection, the function produces no rows for that column:

=> SELECT UNNEST(ARRAY[1,2,null,4]) OVER();
 value
-------
     1
     2

     4
(4 rows)

=> SELECT UNNEST(ARRAY[]::ARRAY[INT]) OVER();
 value
-------
(0 rows)

=> SELECT UNNEST(NULL::ARRAY[INT]) OVER();
 value
-------
(0 rows)

Joining on results

You can use the output of this function as if it were a relation by using CROSS JOIN or LEFT JOIN LATERAL in a query. Other JOIN types are not supported.

Consider the following table of students and exam scores:

=> SELECT * FROM tests;
 student |    scores     |    questions
---------+---------------+-----------------
 Bob     | [92,78,79]    | [20,20,100]
 Lee     |               |
 Pat     | []            | []
 Sam     | [97,98,85]    | [20,20,100]
 Tom     | [68,75,82,91] | [20,20,100,100]
(5 rows)

The following query finds the best test scores across all students who have scores:

=> SELECT student, score FROM tests
CROSS JOIN UNNEST(scores) AS t (score)
ORDER BY score DESC;
 student | score
---------+-------
 Sam     |    98
 Sam     |    97
 Bob     |    92
 Tom     |    91
 Sam     |    85
 Tom     |    82
 Bob     |    79
 Bob     |    78
 Tom     |    75
 Tom     |    68
(10 rows)

The following query returns maximum and average per-question scores, considering both the exam score and the number of questions:

=> SELECT student, MAX(score/qcount), AVG(score/qcount) FROM tests
CROSS JOIN UNNEST(scores, questions) AS t(score, qcount)
GROUP BY student;
 student |         MAX          |       AVG
---------+----------------------+------------------
 Bob     | 4.600000000000000000 | 3.04333333333333
 Sam     | 4.900000000000000000 | 3.42222222222222
 Tom     | 4.550000000000000000 |             2.37
(3 rows)

These queries produce results for three of the five students. One student has a null value for scores and another has an empty array. These rows are not included in the function's output.

To include null and empty arrays in output, use LEFT JOIN LATERAL instead of CROSS JOIN:

=> SELECT student, MIN(score), AVG(score) FROM tests
LEFT JOIN LATERAL UNNEST(scores) AS t (score)
GROUP BY student;
 student | MIN |       AVG
---------+-----+------------------
 Bob     |  78 |               83
 Lee     |     |
 Pat     |     |
 Sam     |  85 | 93.3333333333333
 Tom     |  68 |               79
(5 rows)

The LATERAL keyword is required with LEFT JOIN. It is optional for CROSS JOIN.

Examples

Consider a table with the following definition:

=> CREATE TABLE orders (
        orderkey VARCHAR, custkey INT,
        prodkey ARRAY[VARCHAR], orderprices ARRAY[DECIMAL(12,2)],
        email_addrs ARRAY[VARCHAR]);

The following query expands one of the array columns. One of the elements is null:

=> SELECT UNNEST(orderprices) AS price, custkey, email_addrs
   FROM orders WHERE custkey='342845' ORDER BY price;
 price | custkey |       email_addrs
-------+---------+-------------------------
       |  342845 | ["br92@cs.example.edu"]
 12.00 |  342845 | ["br92@cs.example.edu"]
 22.00 |  342845 | ["br92@cs.example.edu"]
 35.00 |  342845 | ["br92@cs.example.edu"]
(4 rows)

UNNEST can expand more than one column:

=> SELECT orderkey, UNNEST(prodkey, orderprices)
   FROM orders WHERE orderkey='113-341987';
  orderkey  | val_prodkey | val_orderprices
------------+-------------+-----------------
 113-341987 | MG-7190     |           60.00
 113-341987 | MG-7190     |           67.00
 113-341987 | MG-7190     |           22.00
 113-341987 | MG-7190     |           14.99
 113-341987 | VA-4028     |           60.00
 113-341987 | VA-4028     |           67.00
 113-341987 | VA-4028     |           22.00
 113-341987 | VA-4028     |           14.99
 113-341987 | EH-1247     |           60.00
 113-341987 | EH-1247     |           67.00
 113-341987 | EH-1247     |           22.00
 113-341987 | EH-1247     |           14.99
 113-341987 | MS-7018     |           60.00
 113-341987 | MS-7018     |           67.00
 113-341987 | MS-7018     |           22.00
 113-341987 | MS-7018     |           14.99
(16 rows)