BigQuery UNNEST: Turn Arrays Into Rows Safely

By Sharon Ben-Moshe · August 21, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Use BigQuery UNNEST to flatten arrays into rows. Learn CROSS JOIN, LEFT JOIN, WITH OFFSET, aliases, and how to avoid duplicate surprises.

Quick answer

BigQuery UNNEST takes an ARRAY and returns one row per element. For an array column, place UNNEST on the right side of a CROSS JOIN, INNER JOIN, or LEFT JOIN. Use WITH OFFSET when element position matters, and account for the extra rows before adding aggregates.

FAQs

What does UNNEST do in BigQuery?

UNNEST takes an ARRAY and produces one row per element. When it is correlated to an array column, each parent row can expand into several result rows.

Why does UNNEST create duplicate-looking rows?

The parent columns repeat once for every array element. This is expected expansion, not necessarily a duplicate. Aggregate or select at the correct parent-versus-element grain.

When should I use WITH OFFSET in BigQuery UNNEST?

Use WITH OFFSET when an element’s zero-based position in the original array is meaningful, such as the sequence of steps, items, or events.

Should I use CROSS JOIN or LEFT JOIN with UNNEST?

Use CROSS JOIN when parents without elements may disappear from the expanded result. Use LEFT JOIN when the report must retain parent rows even when the array contributes no element, then verify the exact output with empty and NULL arrays.

SQL Dialects