I am migrating to the new SQL syntax in BigQuery, since it seems more flexible. However I am a bit stuck when it comes to access the fields in the customDimensions. I am writing something quite simple like this:

我正在迁移到BigQuery中的新的SQL语法，因为它看起来更灵活。然而，当涉及到访问customDimensions维度中的字段时，我有点不知所措。我写的东西很简单:

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

But I get the error

但我得到了误差

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

However if I run something like this works perfectly fine:

但是，如果我运行这样的程序，效果很好:

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 different logic when it comes to query the customDimensions?

在查询customDimensions时，是否存在一些不同的逻辑?

                       ###2 个解决方案 

                            ####1 

                              <p><i></i>4  <i></i></p>

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

如果目的是在一个扁平的表单中检索所有自定义维度，那么就加入UNNEST(customDimensions):

standardSQL

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

                            ####2 

1  

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 

                              <p><i></i>4  <i></i></p>

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

如果目的是在一个扁平的表单中检索所有自定义维度，那么就加入UNNEST(customDimensions):

standardSQL

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

                            ####2 

1  

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