BigQuery中Google Analytics的Struct转JSON格式问题求助

我完全懂你遇到的困扰——customDimensions作为嵌套数组结构，直接用TO_JSON_STRING输出的是包含index和value字段的数组JSON，而不是我们通常想要的键值对格式JSON。下面给你提供精准的解决方案：

核心思路 #

customDimensions本质是一个包含index（维度索引）和value（维度值）的STRUCT数组，我们需要将其转换为以index为键、value为值的JSON对象。可以用BigQuery的OBJECT_AGG()函数聚合键值对，再用TO_JSON_STRING()转换为JSON字符串。

完整查询语句 #

#standardSQL
SELECT 
  visitNumber, 
  visitId, 
  fullVisitorId,
  -- 将customDimensions数组转换为键值对格式的JSON字符串
  TO_JSON_STRING(
    IFNULL(
      (SELECT OBJECT_AGG(CAST(cd.index AS STRING), cd.value) 
       FROM UNNEST(hits.customDimensions) AS cd),
      {} -- 处理空/无数据的情况，返回空JSON对象
    )
  ) AS customDimensions_json
FROM table_a 
LEFT JOIN UNNEST(hits) AS hits 
LIMIT 10;

为什么之前的方法没生效？ #

• 直接调用TO_JSON_STRING(hits.customDimensions)会输出原始数组结构：[{"index":1,"value":"xxx"},{"index":2,"value":"yyy"}]，不符合键值对需求。
• ARRAY/ARRAY_CONCAT只是对数组做拼接或重组，无法将数组元素转换为对象的键值对。

效果示例 #

如果原始customDimensions数据是：

[{"index":1, "value":"user_123"}, {"index":3, "value":"premium"}]

转换后的customDimensions_json会是：

{"1":"user_123", "3":"premium"}

内容的提问来源于stack exchange，提问作者Lijju Mathew