UNNEST

Expand an array into a set of rows, one per element.

Category: collectionReturns: SETOF TDialect: PostgreSql

Syntax

UNNEST(array)

Description

## Overview Expands an array value into a set of rows, producing one row per array element. UNNEST is the canonical set-returning function for array-to-relation conversion and is the idiomatic bridge when downstream logic requires relational operators (GROUP BY, JOIN, ORDER BY) over per-row collection data. Typical uses include denormalising tag arrays for tag-based aggregations, lateral-joining a per-row event trail with the enclosing row's attributes, and introducing inline row sources from literal or generated arrays. ## Behavior - Returns a set of rows whose column type matches the array's element type. - NULL elements in the array produce rows whose value is NULL. - NULL input arrays produce zero rows (no NULL row is emitted). - Empty input arrays produce zero rows. - When used with WITH ORDINALITY, also returns the 1-based position of each element in the input array. - Typically appears in a FROM clause or LATERAL context. Some dialects also permit a SELECT-list form. ## Null and empty handling - NULL input array produces zero rows (UNNEST is equivalent to EXPLODE in this respect). - Empty input array produces zero rows. - NULL elements inside the input produce rows with NULL values. - To retain the parent row even when the array is empty or NULL, use LEFT JOIN LATERAL UNNEST(...) or EXPLODE_OUTER. ## Compatibility - Matches the SQL standard UNNEST convention. EXPLODE is the functional synonym in the array/map SQL dialects. WITH ORDINALITY is the standard way to retrieve the element index alongside the value. - For per-row lateral expansion, UNNEST and EXPLODE are interchangeable; the choice depends on dialect preference.

Parameters

Name	Type	Description
`array`		Specifies the input array. Each element becomes an independent row in the result set.

Examples

-- Expand an integer array into rows
SELECT UNNEST(ARRAY[1, 2, 3]);  -- rows: 1, 2, 3

-- Use in FROM to introduce a row source
SELECT val FROM UNNEST(ARRAY['a', 'b', 'c']) AS t(val);

-- NULL elements become NULL rows
SELECT UNNEST(ARRAY[10, CAST(NULL AS INT), 30]);  -- rows: 10, NULL, 30

-- Denormalise a per-session event trail into rows
SELECT s.session_id, e.event
FROM analytics.events.user_sessions s,
     UNNEST(s.events) AS e(event);

-- NULL input array produces zero rows
SELECT val FROM UNNEST(CAST(NULL AS ARRAY<INT>)) AS t(val);

-- Empty array also produces zero rows
SELECT val FROM UNNEST(CAST(ARRAY() AS ARRAY<INT>)) AS t(val);

Pitfalls

NULL and empty input arrays both produce zero rows, which drops the parent row in a cross join. Use LEFT JOIN LATERAL or EXPLODE_OUTER to retain parent rows.
NULL elements become NULL-valued rows. Filter with ARRAY_COMPACT upstream if NULL rows are undesirable.
UNNEST without WITH ORDINALITY loses positional information. Use WITH ORDINALITY (or EXPLODE with POSEXPLODE) when element index matters.
Per-row expansion can dramatically increase row counts. Check ARRAY_SIZE distributions before running UNNEST on wide high-cardinality collections.
Some dialects disallow UNNEST in SELECT lists and require it in FROM. Prefer FROM placement for portability.