如何在BigQuery中以列值为键创建JSON对象？

问题场景 #

你需要将查询结果里的key列值作为JSON对象的键，value列值作为对应的值。比如从以下数据：

select key, value from unnest(array<struct<key string, value int64>>[("a", 1), ("b", 2)])

期望每行输出对应的JSON：{"a": 1}、{"b": 2}，但直接使用json '{key, value}'会触发报错：

Invalid JSON literal: syntax error while parsing object key - invalid literal; last read: '{k'; expected string literal at [1:8]

错误原因 #

json '{key, value}'写法是把key当成了固定字面量字符串，而非引用列的实际值。BigQuery解析JSON时，会认为这个未加引号的key不是合法的JSON键名，因此抛出语法错误。

解决方法 #

方法1：使用JSON_OBJECT函数（推荐） #

BigQuery的JSON_OBJECT函数支持动态指定键值对，通过KEY关键字可以直接引用列值作为JSON键：

select JSON_OBJECT(key := value) 
from unnest(array<struct<key string, value int64>>[("a", 1), ("b", 2)])

也可以用更明确的写法：

select JSON_OBJECT(KEY key VALUE value) 
from unnest(array<struct<key string, value int64>>[("a", 1), ("b", 2)])

两种写法都能输出你需要的JSON对象，且无需担心特殊字符转义问题。

方法2：字符串拼接转JSON（兼容方案） #

如果需要适配旧写法，可以先拼接成合法的JSON字符串，再用JSON_PARSE转换：

select JSON_PARSE(format('{"%s": %d}', key, value))
from unnest(array<struct<key string, value int64>>[("a", 1), ("b", 2)])

注意：这种方法需要确保key值不含引号、反斜杠等特殊字符，否则会导致JSON解析失败，因此优先推荐方法1。

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