BigQuery中将嵌套列转换为无列名JSON字符串的实现方法

解决方法 #

你可以通过UNNEST打散数组后配合JSON_OBJECT_AGG聚合函数实现需求，具体查询语句如下：

SELECT 
  id,
  store,
  TO_JSON_STRING(
    (SELECT JSON_OBJECT_AGG(item.f0_, item.f1_) 
     FROM UNNEST(items) AS item)
  ) AS items_json
FROM nested_array_example

逻辑说明 #

1. 先用UNNEST(items)将items列的数组拆分为多条单条键值对记录，你测试数据中插入的匿名STRUCT默认字段名为f0_（存储键名，比如table）、f1_（存储值，比如3）
2. 用JSON_OBJECT_AGG将打散后的键、值聚合为标准JSON对象结构
3. 最后用TO_JSON_STRING将JSON对象转为字符串输出，得到你需要的格式

如果你的STRUCT有自定义的字段名，把上面语句里的f0_、f1_替换成你实际的字段名即可。如果存在重复的键，JSON_OBJECT_AGG会默认保留最后一次出现的键对应的值，有特殊去重需求可以在打散数组时提前做过滤处理。

内容的提问来源于stack exchange，提问作者yonatan gershon