BigQuery error: Cannot query the cross product of repeated fields

Question

I am running the following query on Google BigQuery web interface, for data provided by Google Analytics:

SELECT *
FROM [dataset.table]
WHERE
  hits.page.pagePath CONTAINS "my-fun-path" 

I would like to save the results into a new table, however I am obtaining the following error message when using Flatten Results = False:

Error: Cannot query the cross product of repeated fields customDimensions.value and hits.page.pagePath.

This answer implies that this should be possible: Is there a way to select nested records into a table?

Is there a workaround for the issue found?

Answer 1

Depending on what kind of filtering is acceptable to you, you may be able to work around this by switching to OMIT IF from WHERE. It will give different results, but, again, perhaps such different results are acceptable. The following will remove entire hit record if (some) page inside of it meets criteria. Note two things here:

• it uses OMIT hits IF, instead of more commonly used OMIT RECORD IF).
• The condition is inverted, because OMIT IF is opposite of WHERE

The query is:

SELECT *
FROM [dataset.table]
OMIT hits IF EVERY(NOT hits.page.pagePath CONTAINS "my-fun-path")