In PostgreSQL, we can use the unnest() function to expand an array into a set of rows. We pass the array as an argument, and the function returns each element on a separate row.

We can use the function on multi dimensional arrays, and it’s also possible to use it to unnest multiple arrays by including it in the FROM clause of a query.

Example

Here’s a quick example to demonstrate:

SELECT unnest(array['Cat','Dog','Bird']);

Result:

 unnest 
--------
 Cat
 Dog
 Bird
(3 rows)

Multi Dimensional Arrays

Here’s an example that uses a multi dimensional array:

SELECT unnest(
    array[
        array['Cat','Dog','Bird'], 
        array['Horse','Rabbit','Cow']
        ]);

Result:

 unnest 
--------
 Cat
 Dog
 Bird
 Horse
 Rabbit
 Cow
(6 rows)

Bear in mind that the sub arrays must have matching dimensions. For example, the following produces an error:

SELECT unnest(
    array[
        array['Cat','Dog','Bird'], 
        array['Horse','Rabbit']
        ]);

Result:

ERROR:  multidimensional arrays must have array expressions with matching dimensions

In this case the first array has three elements but the second array only has two, which resulted in an error. That said, it’s possible to unnest multiple arrays by simply passing them as separate arrays (see below).

Unnest Multiple Arrays

We can unnest multiple arrays by including the unnest() function in the FROM clause of a query:

SELECT * FROM unnest(
    array['Cat','Dog','Bird'], 
    array['Horse','Rabbit']) 
    AS result(a,b);

Result:

  a   |   b    
------+--------
 Cat  | Horse
 Dog  | Rabbit
 Bird | null
(3 rows)

Each array is output in its own column. If the arrays are different lengths, then the shorter one is padded with nulls (like we can see in column b).

When we do this, the arrays can have different data types:

SELECT * FROM unnest(
    array[1,2,3], 
    array['Cat','Dog','Bird'], 
    array['Jellymeat','Steak','Seed']) 
    AS result(id,animal,food);

Result:

 id | animal |   food    
----+--------+-----------
  1 | Cat    | Jellymeat
  2 | Dog    | Steak
  3 | Bird   | Seed
(3 rows)