CODEWITHSUNDEEP
sqlgoogle-bigquery
Mark Ginsburg
Mark Ginsburg

Reputation: 2269

How to query a repeating record within a repeating record in Google Big Query Standard SQL

'hits' is a repeating record. So is 'hits.customDimensions' under 'hits'.

I have this Google Big Query in Standard SQL:

SELECT    

       visitNumber,
       h.time, h.hour, h.minute,  
       h.page.PagePath,   


      h.customDimensions.value as language,  /* not working */ 


             from   
                `550335029.ga_sessions_*` , UNNEST(hits) as h  
             where               
                 h.customDimensions.index = 3  /* not working */

I am looking for the right syntax to access hits.customDimensions.index and hits.customDimensions.value. If I remove the two 'not working' lines the query runs.

Error looks like this:

GenericGBQException: Reason: invalidQuery, Message: Cannot access field customDimensions on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [40:46]

Upvotes: 1

Views: 373

Answers (2)

Willian Fuks
Willian Fuks

Reputation: 11787

I also found that you can observe great performances boosts when using Standard SQL and avoid some unnesting operations (but this depends on what you are operating on).

As an example, this is another way of solving this:

SELECT
  visitNumber,
  h.time,
  h.hour,
  h.minute,
  h.page.PagePath,
  (select value from unnest(h.customDimensions) where index = 3) AS LANGUAGE
FROM
  `550335029.ga_sessions_*`,
  UNNEST(hits) AS h
WHERE 1 = 1
AND REGEXP_EXTRACT(_table_suffix, r'.*_(.*)') BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
and exists(select 1 from unnest(h.customDimensions) dim where dim.index = 3)

You won't find much difference for what you are doing now but it's interesting to keep in mind different techniques to operate in BQ as they can eventually make your queries dozens of times faster.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Try below for BigQuery Standard SQL 

SELECT
  visitNumber,
  h.time,
  h.hour,
  h.minute,
  h.page.PagePath,
  d.value AS language
FROM
  `550335029.ga_sessions_*`,
  UNNEST(hits) AS h,
  UNNEST(h.customDimensions) AS d
WHERE d.index = 3

Upvotes: 1

Related Questions