Skip to main content

Blog

BigQuery Tips: Arrays in BigQuery - what they are and how to get data out of them

Katie Kaczmarek10 August 20264 min read

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.

Need to connect your data?

Join marketing, customer and product data with tested models and documentation your team can use. Start with the sources needed for your question.

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.

The toolbox is still growing

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.

Need to connect your data?

Join marketing, customer and product data with tested models and documentation your team can use. Start with the sources needed for your question.

Have a question or project in mind?

Bring data to connect, a question to investigate or something to fix or build. We can agree a focused scope directly.