如何在BigQuery中将整数数组列转换为JSON字典列？

需求说明 #

输入为包含整数数组的列，示例数据：

[1,2,3]
[1,2,3,5,8]

需要转换为JSON类型列，输出格式：

{"1":"1","2":"2","3":"3"}
{"1":"1","2":"2","3":"3","5":"5","8":"8"}

解决方案 #

可以通过UNNEST展开数组、字符串聚合构造JSON格式，再解析为JSON类型来实现，具体SQL代码如下：

WITH sample_data AS (
  -- 模拟源数据
  SELECT [1,2,3] AS int_arr UNION ALL
  SELECT [1,2,3,5,8] AS int_arr
)
SELECT
  int_arr,
  -- 构造JSON字符串并解析为JSON类型
  SAFE.PARSE_JSON(FORMAT('{%s}', STRING_AGG(FORMAT('"%d":"%d"', elem, elem), ','))) AS target_json_col
FROM sample_data, UNNEST(int_arr) AS elem
GROUP BY int_arr;

代码解释 #

• UNNEST(int_arr)：将每行的整数数组拆分为多行，每个数组元素单独占一行；
• FORMAT('"%d":"%d"', elem, elem)：把整数元素转换为"数字":"数字"的字符串格式，符合JSON对象的键值要求（JSON键必须是字符串类型）；
• STRING_AGG(..., ',')：将同一原始行的所有键值对字符串用逗号连接，形成"1":"1","2":"2",...的片段；
• FORMAT('{%s}', ...)：给连接后的片段包裹大括号，生成完整的JSON对象字符串；
• SAFE.PARSE_JSON(...)：将JSON格式的字符串解析为BigQuery的JSON类型列，SAFE_前缀保证在格式异常时返回NULL而非报错。

注意事项 #

如果数组为空，该方法会生成{}空JSON对象；如果数组包含重复元素，聚合后会保留重复的键值对（符合JSON语法，若需去重，可在UNNEST后添加DISTINCT elem）。

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