Error when trying to select event data with BigQuery

Question

I'm trying to select the event_dim.date within BigQuery but am unable to actually access it due to an error

Error: Cannot access field date on a value with type ARRAY<STRUCT<date STRING, name STRING, params ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT, ...>>>, ...>> at [1:18]

My Query:

SELECT event_dim.date FROM `table`

I know I'm doing something wrong and any help would be greatly appreciated. Thanks!

Answer 1

Are you trying to get an array of the dates for each row? Then you want an ARRAY subquery:

SELECT ARRAY(SELECT date FROM UNNEST(event_dim)) AS dates
FROM `table`;

If you are trying to get all dates in separate rows, then you want to cross join with the array:

SELECT event.date
FROM `table`
CROSS JOIN UNNEST(event_dim) AS event;

To filter for a particular date:

SELECT event.date
FROM `table`
CROSS JOIN UNNEST(event_dim) AS event
WHERE event.date = '20170104';

Or you can parse the date string as a date type, which would let you filter on the year or week, for example:

SELECT date
FROM (
  SELECT PARSE_DATE('%Y%m%d', event.date) AS date
  FROM `table`
  CROSS JOIN UNNEST(event_dim) AS event
)
WHERE EXTRACT(YEAR FROM date) = 2017;