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")

Answer 2

Update: see the related thread, I am afraid this is no longer possible. It would be possible to use NEST function and grouping by a field, but that's a long shot.

Using flatten call on the query:

SELECT *
FROM flatten([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910],customDimensions)
WHERE
  hits.page.pagePath CONTAINS "m"  

Thus in the web ui:

• setting a destination table
• allowing large results
• and NO flatten results

does the job correctly and the produced table matches the original schema.

Answer 3

I know - it is old ask. But now it can be achieved by just using standard SQL dialect instead of Legacy

#standardSQL
SELECT t.*
FROM `dataset.table` t, UNNEST(hits.page) as page
WHERE
  page.pagePath CONTAINS "my-fun-path"