Keywords: BigQuery | ARRAY | STRUCT | UNNEST | SQL Query
Abstract: This article discusses common errors when querying nested ARRAY<STRUCT> fields in Google BigQuery and provides a solution using the UNNEST function. It covers the Standard SQL dialect and best practices for handling complex data types.
Introduction
In BigQuery, nested data types such as ARRAY and STRUCT are commonly used to represent hierarchical data. However, querying these fields can lead to errors if not handled properly.
Error Analysis
The error "Cannot access field referer on a value with type ARRAY<STRUCT<...>>" occurs because the field hits is an array of structures. In Standard SQL, you cannot directly access fields within an array without flattening it first.
Solution with UNNEST
To resolve this, use the UNNEST function to flatten the array. For example:
SELECT date, h.referer FROM `refresh.ga_sessions_xxxxxx*`, UNNEST(hits) as hThis query unnests the hits array, allowing you to access the referer field from each structure.
Additional Considerations
Ensure you are using the Standard SQL dialect, as Legacy SQL might have different syntax. Also, be aware of performance implications when unnesting large arrays.
For more complex queries, you can use JOIN with UNNEST or filter the unnested data.