Querying customDimensions in Google Bigquery

Question

Can someone explain how to query customDimensions in bigquery?

My query works with a simple where clause i.e.

SELECT COUNT(DISTINCT fullVisitorId)
    FROM GA_SESSION
     WHERE hits.customDimensions.index = 4
     AND hits.customDimensions.value = 'variable1'
    AND hits.page.pagePath LIKE '%something%';

However now when I try to do an aggregates, or if I try EXACT_COUNT_DISTINCT without a group by, I get an error i.e.

SELECT date, COUNT(DISTINCT fullVisitorId)
FROM GA_SESSION
 WHERE hits.customDimensions.index = 4
 AND hits.customDimensions.value = 'variable1'
AND hits.page.pagePath LIKE '%something%'
group by date

Error is : Query Failed Error: Cannot query the cross product of repeated fields customDimensions.index and hits.customDimensions.index.

Answer 1

Those are repeated fields, and you need to FLATTEN them in order to query.

FLATTEN converts a repeated field into an optional field. Given one record with many values for a repeated field, FLATTEN unrolls it into many records, one record for each value of the (formerly) repeated field; any non-repeated fields become duplicated to fill out each of the new records formed. FLATTEN removes one level of nesting.

Read more about FLATTEN

You need to rewrite your query into:

SELECT .... FROM FLATTEN(FLATTEN(GA_SESSION,customDimensions),hits.customDimensions)