If you have ever opened a GA4 export in BigQuery for the first time and found yourself looking at a column called event_params that seemed to contain an entire table inside each row, this post is for you.
Arrays are not trying to make your life harder. Once you understand why they exist and what to do with them, they stop being intimidating.
Why BigQuery uses arrays
GA4 tracks events. Each event can have many parameters. A purchase event might have a transaction ID, a value, a currency and a list of items, each item with its own name, price and quantity. Rather than create hundreds of columns to cover every possible combination, GA4 stores related values together in arrays.
This means event_params is not a broken column. It is a structured collection of key-value pairs sitting inside each row. The same applies to items for purchase data and user_properties for user-level attributes.
Getting data out: UNNEST
The core operation for working with arrays is UNNEST. It expands an array column into individual rows so you can filter and select from it like a normal table.
The most common pattern in GA4 data is a correlated subquery, pulling a single parameter value out inline:
SELECT
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `project.dataset.events`
If you need to filter on a parameter or work with multiple values from the same array, the comma join is cleaner:
SELECT
event_name,
ep.value.string_value AS page_location
FROM `project.dataset.events`,
UNNEST(event_params) AS ep
WHERE ep.key = 'page_location'
One thing worth knowing: the comma syntax is a cross join. If a row has an empty array, that row is dropped from the results. If you need to keep rows with empty arrays, use LEFT JOIN UNNEST instead.
The SAFE_OFFSET trap
When you click an array column in the BigQuery UI it often auto-fills something like:
ecomm_events[SAFE_OFFSET(0)].transaction_id
SAFE_OFFSET(0) picks the element at position zero — the first item in the array. It returns NULL rather than erroring if the array is empty, which is why it looks safe. The problem is that it only ever looks at that one position. If the value you want is not at position zero, you get NULL and no indication that anything went wrong.
For picking a specific position when you are certain the structure is consistent, SAFE_OFFSET is fine. For searching across all elements in an array, use UNNEST with a filter:
SELECT ds.*
FROM `project.dataset.ga4_daily_snapshot` AS ds,
UNNEST(ds.ecomm_events) AS ee
WHERE ee.transaction_id = 'abc123'
The new shorthand: ARRAY_FIRST and ARRAY_LAST
BigQuery recently added ARRAY_FIRST() and ARRAY_LAST(). If you have an ordered array and want the first or last element, you no longer need to reach for SAFE_OFFSET:
-- Old way
session_attribution[SAFE_OFFSET(0)]
session_attribution[SAFE_OFFSET(ARRAY_LENGTH(session_attribution) - 1)]
-- New way
ARRAY_FIRST(session_attribution)
ARRAY_LAST(session_attribution)
These make the most sense when your array already has a meaningful order. For example, attribution events sorted by timestamp. First and last then map directly to first touch and last touch:
SELECT
session_id,
ARRAY_FIRST(attribution_events) AS first_touch,
ARRAY_LAST(attribution_events) AS last_touch
FROM sessions
One thing to watch: unlike SAFE_OFFSET, there is no SAFE_ARRAY_FIRST() or SAFE_ARRAY_LAST(). If the array is empty, these functions error rather than returning NULL. If your data can have empty arrays, check with ARRAY_LENGTH first or wrap in a CASE.
Building arrays: ARRAY_AGG
The functions above are for reading arrays. ARRAY_AGG is for building them, aggregating individual rows into an array, usually with an order:
SELECT
session_id,
ARRAY_AGG(page_path ORDER BY event_timestamp ASC) AS page_sequence
FROM events
GROUP BY session_id
This gives you one row per session with every page visited in order, a pattern that comes up constantly in session analysis.
BigQuery is actively adding more array functions. ARRAY_FILTER for keeping only elements that meet a condition and ARRAY_TRANSFORM for applying a function to every element are in the pipeline. Worth keeping an eye on the BigQuery release notes, these are the kind of updates that quietly save you from writing a lot of verbose UNNEST logic.
