How to Unnest Arrays in BigQuery
Last updated August 23, 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 freeDo more with BigQuery
Switching SQL clients?
Side-by-side roundups with prices checked against each vendor.
Related BigQuery guides
- How to Convert an Array to a String in BigQuery
- How to Use Arrays in BigQuery
- How to Query JSON in BigQuery
- How to Use REGEXP_EXTRACT in BigQuery
- How to Split a String into an Array in BigQuery
- How to Get Array Length in BigQuery
- How to Unnest event_params in GA4 BigQuery Data
- How to Use GENERATE_DATE_ARRAY in BigQuery
