Unable to filter using WHERE (Cannot access field hour on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64)

Question

#standardSQL
SELECT DISTINCT geoNetwork.country
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170701' 
AND
hits.hour > '11'

I'm trying to view list of countries that sent hits to Google Analytics between 12 noon and midnight (in Standard SQL) I'm using hits.hour from https://support.google.com/analytics/answer/3437719?hl=en but get this error: Cannot access field hour on a value with type ARRAY

Answer 1

#standardSQL
SELECT DISTINCT geoNetwork.country
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170701' 
AND EXISTS (SELECT 1 FROM UNNEST(hits) hit WHERE hit.hour > 11)