Cannot access field in Big Query with type ARRAY>

Question

I'm trying to run a query using Standard SQL Dialect (ie not Legacy SQL) on BigQuery. My query is:

SELECT
date, hits.referer
FROM `refresh.ga_sessions_xxxxxx*`
LIMIT 1000

But keep getting the error

Error: Cannot access field referer on a value with type 
ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [2:12]

Anyone know the proper syntax?

score 139 · Accepted Answer · answered Aug 23 '16 at 20:28

139

if you are looking for all referers - try

SELECT date, h.referer
FROM `refresh.ga_sessions_xxxxxx*`, UNNEST(hits) as h

answered Aug 23 '16 at 20:28

Mikhail Berlyant

2

I am getting this `Error: Syntax error: Unexpected keyword UNNEST` when I am using the function `UNNEST`. I am running the query in Standard SQL Dialect only – Rito May 09 '18 at 07:07
2

Ok I found the mistake, I was using `UNNEST` in the select, it can be used only with `FROM`. – Rito May 09 '18 at 07:21
11

For those coming here trying to work on firebase here's a nice blogpost: https://firebase.googleblog.com/2017/03/bigquery-tip-unnest-function.html – Palash Bansal Aug 10 '19 at 15:06
`UNNEST`: `UNNEST` takes an ARRAY and returns a table with a single row for each element in the `ARRAY`. Resource: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays – prayagupa Dec 07 '21 at 19:34

1 Answers1