UNNEST

UNNEST can expand ARRAY or MAP to form a relation.

ARRAYS is expanded into a single column, and MAP is expanded into two columns (key, value).

UNNEST can also be used together with multiple parameters, which will be expanded into multiple columns with the same number of rows as the maximum base parameter (other columns are filled with nulls).

UNNEST can choose to use the WITH ORDINALITY clause, in which case an additional ORDINALITY column is added at the end.

UNNEST is usually used together with JOIN, which can reference columns in the left relationship of JOIN.

• Using a Separate Column

  SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t (score);

• Using Multiple Columns

  SELECT numbers, animals, n, a
  FROM (
  VALUES
  (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
  (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
  ) AS x (numbers, animals)
  CROSS JOIN UNNEST(numbers, animals) AS t (n, a);