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

> 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 an

Source: https://www.measurelab.co.uk/insights/blog/bigquery-tips-arrays-in-bigquery-what-they-are-and-how-to-get-data-out-of-them/  
Author: Katie Kaczmarek  
Published: 2026-08-10  
Updated: 2026-10-13

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.

### **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.
