UNNEST for arrays

Concepts covered: Array Flattening

An array column stores a list inside one cell. The accounts table carries a features column of type ARRAY(VARCHAR), so Northwind's row holds ['sso', 'audit_log', 'sla'] in a single field. That shape is compact to store and awkward to query: COUNT cannot count inside a cell, and GROUP BY cannot group by an element. UNNEST turns an array into a table with one row per element, and joined to the row it came from, it repeats that row once per element. The grain of the result changes from one row per account to one row per account and feature.

Three accounts become 6 rows

The 3 Team accounts from the first section carry these arrays: Northwind has 3 features, Acme has 1 and Globex has 2. The query below unnests them. The alias f(feature) names the derived table and its single column. CROSS JOIN is the join type because UNNEST here is a lateral source: it produces rows for the current outer row only, so there is no condition to write.

SELECT a.account_id, a.name, a.seats, f.feature
FROM accounts AS a
CROSS JOIN UNNEST(a.features) AS f(feature)
WHERE a.plan = 'team'
ORDER BY a.account_id, f.feature;

Six rows come out of 3. Northwind's seat count, 10, now appears on 3 rows, and Globex's 40 on 2. Every column of the account is copied onto each element's row. That keeps per-feature figures right, and it turns any total taken across features into a double count.

Aggregating at the new grain

SELECT f.feature,
       COUNT(*) AS accounts,
       SUM(a.seats) AS seats_after_unnest
FROM accounts AS a
CROSS JOIN UNNEST(a.features) AS f(feature)
WHERE a.plan = 'team'
GROUP BY f.feature
ORDER BY f.feature;

COUNT(*) per feature is correct: each row is one account holding one feature, so counting rows counts accounts per feature. The seat column tells a different story. Adding the 3 figures gives 135 seats, against a true total of 75, because Northwind's 10 seats were counted 3 times and Globex's 40 twice. Any aggregate of an account-level column has to happen before the unnest, or against DISTINCT account_id after it, or it inflates by the array length of each row. Per-feature seat totals are legitimate when the question is how many seats each feature covers, and wrong the moment they are summed.

Position, empty arrays and NULL

SELECT a.account_id, f.feature, f.pos
FROM accounts AS a
LEFT JOIN UNNEST(a.features) WITH ORDINALITY AS f(feature, pos)
  ON TRUE;

WITH ORDINALITY adds the element's 1-based position, so the first feature an account enabled, when the array is kept in enable order, is the row with pos = 1. An empty array produces zero rows, and so does a NULL array, which means CROSS JOIN UNNEST drops an account with no features the way an inner join drops an unmatched row. LEFT JOIN UNNEST ... ON TRUE keeps the account with a NULL feature, which matters whenever the report has to list every account.

The syntax varies by engine more than most of SQL does. Trino and PostgreSQL both accept CROSS JOIN UNNEST(features) AS f(feature) and WITH ORDINALITY; PostgreSQL also allows unnest(features) in the select list. BigQuery writes CROSS JOIN UNNEST(features) AS feature, with the alias naming the element directly, and Snowflake uses LATERAL FLATTEN. The behaviour is the same in all of them: one row per element, the outer row copied alongside.

Drill: expand the features

The drill hands over an accounts table with a features array column and a query that selects the account and its feature but is missing its join. Supply the clause that produces one row per account and feature.

About this lesson

This section is part of the Query Structure: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.