BigQuery: Querying repeated fields

4
votes

I'm trying to use the following query to get the rows for which the event names are equal to: EventGamePlayed, EventGetUserBasicInfos or EventGetUserCompleteInfos

select *
from [com_test_testapp_ANDROID.app_events_20170426]
where event_dim.name in ("EventGamePlayed", "EventGetUserBasicInfos", "EventGetUserCompleteInfos");

I'm getting the following error: Cannot query the cross product of repeated fields event_dim.name and user_dim.user_properties.value.index.

Is it possible to make it work by not having a flattened result ? Also, I'm not sure why the error is talking about the "user_dim.user_properties.value.index" field.

1
google-bigquery

1 Answers

5
votes

The error is due to the SELECT *, which includes all columns. Rather than using legacy SQL, try this using standard SQL, which doesn't have this problem with repeated field cross products:

#standardSQL
SELECT *
FROM com_test_testapp_ANDROID.app_events_20170426
CROSS JOIN UNNEST(event_dim) AS event_dim
WHERE event_dim.name IN ("EventGamePlayed", "EventGetUserBasicInfos", "EventGetUserCompleteInfos");

You can read more about working with repeated fields/arrays in the Working with Arrays topic. If you are used to using legacy SQL, you can read about differences between legacy and standard SQL in BigQuery in the migration guide.