BigQuery SQL: Cannot query the cross product of repeated fields /

Question

My goal is to filter from all visitors, to only analyse customers (which are in the customDimension.index =2 and then further filter only specific types of pageviews for the customers.

SELECT customDimensions.value AS CustomerID,
SUM(totals.pageviews) as page_views,
SUM(CASE WHEN hits.type = 'PAGE' AND hits.contentGroup.contentGroup2 = 'important' THEN 1 ELSE 0 END) AS important_pageviews
FROM `xxxxxxxx.ga_sessions_20180415`
WHERE customDimensions.index = 2
GROUP BY CustomerID

I get the error that (using StandardSQL):

Error: Cannot access field index on a value with type
ARRAY<STRUCT<index INT64, value STRING>> at [5:24]

For Legacy SQL:

Error: Cannot query the cross product of repeated fields customDimensions.index and hits.contentGroup.contentGroup2.

Edit:

SELECT cd.value AS CustomerID,
SUM(totals.pageviews) as page_views,
SUM(CASE WHEN hits.type = 'PAGE' AND hits.contentGroup.contentGroup2 = 'important' THEN 1 ELSE 0 END) AS important_pageviews
FROM `xxxxxxxx.ga_sessions_20180415`,
UNNEST(customDimensions) AS cd
WHERE cd.index = 2
GROUP BY CustomerID

returns:

Error: Cannot access field type on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [3:20]

I tried correcting the line 3:20 using UNNEST(hits.type) = 'PAGE' AND UNNEST(hitscontentGroup.contentGroup2) = 'important' which gives Error: Syntax error: Unexpected keyword UNNEST at [3:15]

I guess you are looking for [unnest](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest). `FROM xxxxxxxx.ga_sessions_20180415, unnset(customDimensions) as cd where cd.index = 2` (also you can unnest in an Subquery to preserver number of rows ) — dani herrera, Apr 16 '18 at 10:02
@danihp I still get the same error (standard SQL) - Error: Cannot access field value on a value with type ARRAY> at [1:25] — GRS, Apr 16 '18 at 10:08
@GRS . . . You have an *array*. You need to extract one or more elements from the array for the query. It would help if we could see what the data looks like. — Gordon Linoff, Apr 16 '18 at 10:46

score 1 · Accepted Answer · answered Apr 16 '18 at 11:06

1

As customDimensions is an array you'll need to unnest this in order to refer to it's contents, see the StandardSQL example below where I unnest UserIDs from Google Analytics data in BigQuery:

SELECT customDimension.value AS UserID
FROM `my.project.data` AS t
  CROSS JOIN UNNEST(t.customdimensions) AS customDimension
  WHERE customDimension.index = 2

answered Apr 16 '18 at 11:06

Ben P

3,267
4
26
53

thanks, but I'm trying to combine 2 unnests together. After adding on the second line `t.hits.contentGroup.contentGroup2`, to get data about the number of pages viewed by these customers, I return to my error. – GRS Apr 16 '18 at 11:28
Sorry, where is your second unnest, I can only see one? You can add another unnest directly after the first to unnest further? – Ben P Apr 16 '18 at 11:53

BigQuery SQL: Cannot query the cross product of repeated fields /

1 Answers1