Clever Geek Handbook

BigQuery to access CustomDimensions with new SQL syntax

I am migrating to the new SQL syntax in BigQuery as it looks more flexible. However, I am a bit stuck when it comes to fields in customDimensions. I am writing something very simple:

SELECT 
  cd.customDimensions.index,
  cd.customDimensions.value
FROM `xxxxx.ga_sessions_20170312`, unnest(hits) cd
limit 100

But I am getting error

Error: Cannot access field index on a value with type ARRAY<STRUCT<index INT64, value STRING>>

However, if I run something like this, it works great:

    SELECT 
        date,
        SUM((SELECT SUM(latencyTracking.pageLoadTime) FROM UNNEST(hits))) pageLoadTime,
        SUM((SELECT SUM(latencyTracking.serverResponseTime) FROM UNNEST(hits))) serverResponseTime
   FROM `xxxxxx.ga_sessions_20170312`
    group by 1

Is there some other logic when it comes to requesting customDimensions?

+3


source to share


2 answers


If the intention is to fetch all custom dimensions in a flattened form, then join with UNNEST(customDimensions)

:



#standardSQL
SELECT 
  cd.index,
  cd.value
FROM `xxxxx.ga_sessions_20170312`,
  unnest(hits) hit,
  unnest(hit.customDimensions) cd
limit 100;
+4


source 


SELECT
fullvisitorid,
( SELECT MAX(IF(index=1,value, NULL))FROM UNNEST(hits.customDimensions)) AS CustomDimension1,
( SELECT MAX(IF(index=2,value, NULL))FROM UNNEST(hits.customDimensions)) AS CustomDimension2
FROM
  `XXXXXXX`, unnest(hits) as hits


+1


source






More articles:


All Articles