Cannot access field on a value with type ARRAY

You wrote array.field, but a dot only reaches into a STRUCT, and the column is a repeated one. The message prints the array type, which shows what to unnest. This is the error GA4 and Universal Analytics exports produce for hits.page and event_params.value.

Cannot access field a on a value with type ARRAY<STRUCT<a INT64>> at [2:14]

What BigQuery means

A dot reaches into a STRUCT. The column in front of the dot is an ARRAY, which is a list of structs, and a list has no fields. BigQuery will not pick an element for you or map the field over every element.

The type in the message is the useful part. Read it from the outside in:

• ARRAY<STRUCT<...>> is a repeated record. Each row holds zero or more structs.
• The names inside STRUCT<...> are the fields you can reach after you have one struct in hand.
• The position points at the field name after the dot. It does not point at the array.
• A struct with more than three fields is cut off after the third, and ... stands for the rest. At every nesting level the message shows at most three fields.

The error names the first array on the path. In hits.page.pagePath, the message complains about page, because hits is the array. Fix that one and the next dot works.

The smallest query that reproduces it

WITH t AS (SELECT [STRUCT(1 AS a), STRUCT(2 AS a)] AS items)
SELECT items.a FROM t

items has two structs in one row. items.a would have to be two values, and a column cannot hold that.

The fix

You have three ways out, depending on what you want.

One output row per element. Unnest the array in FROM. This is the answer most of the time:

SELECT item.a
FROM t, UNNEST(t.items) AS item

The comma is a cross join with each row’s own array, so rows with an empty array disappear. Use LEFT JOIN UNNEST(t.items) AS item to keep them with a NULL. BigQuery also accepts the shorthand FROM t, t.items AS item.

One element, picked by position. Subscript the array, then use the dot:

SELECT items[OFFSET(0)].a AS first_a
FROM t

OFFSET(0) fails when the array is shorter. SAFE_OFFSET(0) gives NULL instead. A bare items[0] also works and means the same as OFFSET(0).

A value computed over the elements. Keep one row per source row and use a subquery:

SELECT
  (SELECT SUM(i.a) FROM UNNEST(items) AS i) AS total,
  ARRAY(SELECT i.a FROM UNNEST(items) AS i) AS all_a
FROM t

This avoids the row multiplication of a join, so it is also the safe choice when you are already aggregating at the row level. EXISTS (SELECT 1 FROM UNNEST(items) AS i WHERE i.a = 2) filters rows by a condition on any element.

GA4 and Universal Analytics exports

Most people meet this on exports where whole tables are built from repeated records.

Universal Analytics sessions keep every page view in hits, and each hit has a page struct:

WITH s AS (
  SELECT 'v1' AS id,
    [STRUCT(STRUCT('/home' AS pagePath) AS page, 1 AS hitNumber),
     STRUCT(STRUCT('/cart' AS pagePath) AS page, 2 AS hitNumber)] AS hits
)
SELECT id, hit.page.pagePath
FROM s, UNNEST(hits) AS hit

Writing hits.page.pagePath on the real sample table bigquery-public-data.google_analytics_sample.ga_sessions_20170801 fails with Cannot access field page on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [1:13]. Only hits is an array. After UNNEST, hit.page is a plain struct and the second dot is fine.

GA4 stores event parameters as ARRAY<STRUCT<key STRING, value STRUCT<...>>>. Writing event_params.value.string_value on bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131 fails with Cannot access field value on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [1:21]. Pick the parameter by key:

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page
FROM e

user_properties has the same key and value shape. items is a plain repeated record, so unnest it: items.item_id fails with Cannot access field item_id on a value with type ARRAY<STRUCT<item_id STRING, item_name STRING, item_brand STRING, ...>> at [1:14].

The variants of the message

The text changes with the type that was in front of the dot.

The error appears wherever the expression is: the SELECT list, WHERE, ORDER BY and ON clauses all report it, with the position of the field name.

A field that does not exist is not reported by this error. Against a plain struct, BigQuery says Field name b does not exist in STRUCT<a INT64>. Against an array, a wrong field name still gives Cannot access field, so check the array first and the spelling second.

Queries that look wrong but run

Naming the unnest alias like the column. The alias wins inside the query:

SELECT items.a FROM t, UNNEST(items) AS items

It works, but items.a now means the unnested struct, not the original column. Rename the alias to the singular, as in item, so you can still see both.

A dot on an array element. Once you have one struct, any depth of dots is fine:

SELECT item.inner_s.z FROM t, UNNEST(items) AS item

Different errors that look like this one

Field name b does not exist in STRUCT<a INT64>. The column is a struct and the field name is wrong.

Dot-star is not supported for type ARRAY<STRUCT<a INT64>>. items.* has the same cause as this page. Unnest first, then item.*.

Values referenced in UNNEST must be arrays. UNNEST contains expression of type INT64. The opposite mistake: you unnested something that is not an array.

Equality is not defined for arguments of type ARRAY<INT64>. You compared a whole array with =. See No matching signature for operator.

If the column is JSON, a dot reaches into it, and that is a different feature. A STRING that holds JSON needs PARSE_JSON first. Writing s.a on a STRING gives the STRING row in the table above.

In Querylab.io

The editor’s own checks do not mark this one as you type. It is a compile error, so BigQuery’s dry run reports it without reading data, and Querylab.io shows dry-run errors when you pause typing. The message is BigQuery’s own, with the array type cut off after three fields. The failed-run panel has no one-click fix for it.

Autocomplete does help while you write the query: after item. on an unnested alias it offers the struct’s fields. See Arrays and Structs.