Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>

sql nested google-bigquery
Feynman27 picture Feynman27 · Aug 23, 2016 · Viewed 53.4k times · Source

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?

Answer

Mikhail Berlyant picture Mikhail Berlyant · Aug 23, 2016

if you are looking for all referers - try 

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

Related questions

Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn't the inner select …

Read More
Best way to do nested case statement logic in SQL Server

I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/…

Read More
MySQL Nested Select Query?

Ok, so I have the following query: SELECT MIN(`date`), `player_name` FROM `player_playtime` GROUP BY `player_name` I then need to use this result inside the following query: SELECT DATE(`date`) , COUNT(DISTINCT `player_name`) FROM `player_playtime /*…

Read More