SQL JSON解析：如何根据值查找对应的键？

针对从JSON列中根据指定值找出对应键的需求，以下是几种主流数据库的实现方案：

PostgreSQL #

利用json_each_text（或jsonb_each_text，对应jsonb类型列）将JSON键值对展开为行，再筛选匹配值的键：

-- 匹配值为["val3"]的情况
SELECT json_key
FROM my_table,
     json_each_text(my_col) AS j(json_key, json_value)
WHERE json_value = '["val3"]';

-- 匹配数组中包含val3的情况（更灵活）
SELECT json_key
FROM my_table,
     json_each(my_col) AS j(json_key, json_value)
WHERE 'val3' = ANY(json_value::text[]);

MySQL（8.0+） #

通过JSON_TABLE将JSON键转为行，再逐个取值对比：

-- 匹配值为["val3"]的情况
SELECT j.key_name
FROM my_table,
     JSON_TABLE(
         JSON_KEYS(my_col),
         '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')
     ) AS j
WHERE JSON_VALUE(my_col, CONCAT('$.', j.key_name)) = '["val3"]';

-- 匹配数组中包含val3的情况
SELECT j.key_name
FROM my_table,
     JSON_TABLE(
         JSON_KEYS(my_col),
         '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')
     ) AS j
WHERE JSON_CONTAINS(my_col, '"val3"', CONCAT('$.', j.key_name));

SQL Server #

使用OPENJSON解析JSON结构，按需展开数组后筛选：

-- 匹配值为["val3"]的情况
SELECT [key] AS json_key
FROM my_table
CROSS APPLY OPENJSON(my_col)
WHERE value = '["val3"]';

-- 匹配数组中包含val3的情况
SELECT j1.[key] AS json_key
FROM my_table
CROSS APPLY OPENJSON(my_col) AS j1
CROSS APPLY OPENJSON(j1.value) AS j2
WHERE j2.value = 'val3';

注意事项 #

• 不同数据库的JSON函数语法差异较大，需根据实际使用的数据库选择对应语句。
• 若JSON数组可能包含多个值，优先选择展开数组后匹配的写法，确保不会遗漏符合条件的键。

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