如何在Google BigQuery中提取JSON对象中指定列存储键对应的值

问题说明 #

现有一张表，其中C1列存储JSON对象，C2列存储需要提取的键名称，目标是从C1的JSON对象中提取C2指定键对应的值。尝试的SQL语句均报错，报错信息为：JSONPath必须是字符串或查询参数

针对不同数据库的解决方法 #

MySQL/MariaDB

使用JSON_EXTRACT结合CONCAT构造动态JSON路径，并用JSON_UNQUOTE去除结果的引号：

SELECT JSON_UNQUOTE(JSON_EXTRACT(C1, CONCAT('$.', C2))) AS extracted_value
FROM MY_TABLE;

或使用更简洁的->>运算符（等价于JSON_UNQUOTE(JSON_EXTRACT(...))）：

SELECT C1->>CONCAT('$.', C2) AS extracted_value
FROM MY_TABLE;

PostgreSQL

PostgreSQL的JSON/JSONB类型支持直接用列作为键提取值，使用->>运算符即可：

-- 适用于json类型列
SELECT C1->>C2 AS extracted_value
FROM MY_TABLE;

-- 适用于jsonb类型列（推荐使用jsonb获得更好性能）
SELECT C1->>C2 AS extracted_value
FROM MY_TABLE;

BigQuery

使用JSON_EXTRACT_SCALAR函数配合动态拼接的路径：

SELECT JSON_EXTRACT_SCALAR(C1, CONCAT('$.', C2)) AS extracted_value
FROM MY_TABLE;

错误原因说明 #

之前尝试的语句失败，是因为部分数据库的JSON_EXTRACT函数要求路径参数为字符串常量或支持动态表达式的格式，直接传入列或未正确处理的拼接表达式不符合函数参数要求，需要根据数据库特性选择对应的运算符或函数来实现动态键提取。

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