How to Unnest Arrays in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
Use FROM table, UNNEST(array_column) AS element to turn each array element into its own row, joined back to the original row.
SELECT order_id, item
FROM orders, UNNEST(line_items) AS item;Keep the array index
SELECT order_id, item, item_offset
FROM orders, UNNEST(line_items) AS item WITH OFFSET AS item_offset;Standalone UNNEST
UNNEST also works on a literal array, useful for generating a small lookup set without a real table.
SELECT status
FROM UNNEST(['pending', 'shipped', 'cancelled']) AS status;The comma between orders and UNNEST(...) is a cross join, but since it correlates to line_items on that same row, you get one output row per array element, not a full cartesian product.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free