UNNEST and Aggregating

PostgreSQL lets you store arrays inside a single column. But sometimes you need to work with each element individually — that is where UNNEST comes in.

UNNEST is a set-returning function that expands an array into a set of rows, one row per element. Think of it as the opposite of aggregation: instead of collapsing many rows into one, it explodes one value into many rows.

The simplest use of UNNEST is passing an array literal directly. Each element becomes its own row in the result set.

Here we expand a plain array of integers into individual rows: