How to Use Arrays in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
ARRAY_LENGTH gets the element count, array[OFFSET(n)] or array[ORDINAL(n)] reads one element, and ARRAY_AGG collects grouped values back into an array.
SELECT order_id,
ARRAY_LENGTH(line_items) AS item_count,
line_items[OFFSET(0)] AS first_item
FROM orders;OFFSET vs ORDINAL
OFFSET is zero-based, matching most programming languages. ORDINAL is one-based, matching how people naturally count. Pick one and stay consistent.
SELECT line_items[ORDINAL(1)] AS first_item
FROM orders;Building arrays with ARRAY_AGG
SELECT customer_id, ARRAY_AGG(order_id ORDER BY order_date DESC) AS order_ids
FROM orders
GROUP BY customer_id;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