How to Unnest Event Parameters in BigQuery

Almost everything you actually want from the GA4 export – the page, the session ID, the value – hides inside a single packed column called event_params. Reaching it means learning one move: UNNEST. Here is how to do it right.

Why the data is packed away

event_params is a repeated RECORD, meaning each event carries an array of key/value pairs rather than tidy columns. The value itself is a struct with four typed slots – string_value, int_value, float_value, double_value – and GA4 fills whichever one fits. So pulling a parameter means finding the right key and reading the right typed slot.

The subquery pattern

The cleanest way to pull one parameter is a scalar subquery over the array:

SELECT
  event_date,
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS session_id
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260107'

Each subquery unnests the array for that row and grabs one key, and picking the correct typed slot matters – ga_session_id lives in int_value, not string_value.

Mind the value type

The most common mistake is reading the wrong slot and getting all NULLs. String parameters like page_location sit in string_value; numeric ones like ga_session_id or engagement_time_msec sit in int_value. If a column comes back empty, check the type before you assume the data is missing.

When you need many parameters

Repeating a subquery per parameter is fine for a few fields. When you need several at once, a common approach is one subquery each, kept in a staging model so downstream queries read flat columns:

SELECT
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_title') AS page_title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source')     AS source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium')     AS medium
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20260115'
  AND event_name = 'page_view'

Keep the suffix filter

Notice the _TABLE_SUFFIX filter on every example. Without it you scan every day you have ever collected, which is slow and, on the $6.25-per-TB on-demand rate, expensive. Filter the date range first, then unnest.

Everything you wanted was packed inside event_params, and UNNEST is the single move that unpacks it. Use a scalar subquery per key, read the correct typed slot, and always filter the date range first. Do that and the column that hid your data becomes the column that hands it over.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.