如何在BigQuery SQL中实现动态键的Map类型JSON输出？

当然可以实现！当你需要从动态的键值对数据生成单个JSON对象时，BigQuery的OBJECT_AGG函数就是完美的解决方案——它专门用来聚合键值对构建动态对象，正好对应你想要的类似Avro Map的效果。

具体实现步骤 #

针对你给出的示例数据：

-- 源数据
SELECT * FROM (
  SELECT "key1" as key, "val1" as val UNION ALL 
  SELECT "key2" as key, "val2" as val
)

只需要用OBJECT_AGG(key, val)聚合所有键值对，再通过TO_JSON_STRING转成JSON字符串即可：

SELECT TO_JSON_STRING(OBJECT_AGG(key, val)) AS dynamic_json
FROM (
  SELECT "key1" as key, "val1" as val UNION ALL 
  SELECT "key2" as key, "val2" as val
)

执行结果 #

运行上面的SQL后，你会得到完全符合预期的输出：

{"key1": "val1", "key2": "val2"}

补充说明 #

• 如果源数据中有重复的键，OBJECT_AGG会保留最后出现的那个键值对。比如下面的查询：

  SELECT TO_JSON_STRING(OBJECT_AGG(key, val)) AS dynamic_json
  FROM (
    SELECT "key1" as key, "val1" as val UNION ALL 
    SELECT "key1" as key, "val2" as val
  )
  

  结果会是{"key1": "val2"}。
• 对比固定键的场景：STRUCT适合键名提前确定的情况，而OBJECT_AGG则完全适配键名来自数据列的动态场景，两者搭配可以覆盖绝大多数JSON生成需求。

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