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
Nasia ntalla
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
Elliott brossard
source
to share
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
Harish
source
to share