UNNEST

UNNEST is a useful utility to expand an ARRAY or a MAP into individual rows of data. The resulting output column will contain the individual data in the array mapped to existing non-ARRAY columns. UNNEST is typically used with a JOIN and can work in single and multiple column instances

UNNEST Example

Suppose we have a boardgamepoints dataset with years where the points by player are in an ARRAY. The data looks like this:

SELECT * 
FROM boardgamepoints

 player |    points
-------+---------------
Lato   | {4,5,1}
Mario  | {9,1,5}
Kalie  | {7,2,3}
Leo    | {5,2,3,9}
Jeny   | {5,2,0,1,2,4}
Pro    | {1,2,0,1,4}
(6 rows)

UNNESTING the Points Column

One simple implementation of unnest can be to simply pass the column of arrays into the UNNEST() function. The result will be a row level ordering of individual values from the points column.

SELECT UNNEST(points) as points 
FROM boardgamepoints;

 unnest
--------
    4
    5
    1
    9
    1
    5
    7
    2
    3
    5
    2
    3
    9
    5
    2
    0
    1
    2
    4
    1
    2
    0
    1
    4
(24 rows)

While this is often useful, it is not easy to then map these points to the individuals. To solve for this, we can do a CROSS JOIN UNNEST

CROSS JOIN UNNEST

The CROSS JOIN UNNEST splits an array column and maps it back to the original indeces allowing for referencing the values back to original ids or observation. This way, we can develop associated computations at the index/id level. The example below demonstrates this implementation

SELECT player, score 
FROM boardgamepoints 
CROSS JOIN UNNEST(points) AS t(score);

 player | score
-------+-------
Lato   |     4
Lato   |     5
Lato   |     1
Mario  |     9
Mario  |     1
Mario  |     5
Kalie  |     7
Kalie  |     2
Kalie  |     3
Leo    |     5
Leo    |     2
Leo    |     3
Leo    |     9
Jeny   |     5
Jeny   |     2
Jeny   |     0
Jeny   |     1
Jeny   |     2
Jeny   |     4
Pro    |     1
Pro    |     2
Pro    |     0
Pro    |     1
Pro    |     4
(24 rows)