如何在MySQL中根据JSON对象指定key名称获取对应value

需求是从存储在MySQL JSON列的对象中，提取数组里name属性等于指定值（如"hit"）对应的value值，示例JSON如下：

{
"f2": [
    {"name":"f21","value":"foo_21"},
    {"name":"hit","value":"foo_hit"},
    {"name":"f22","value":"foo_22"}
]
}

预期结果为带双引号的"foo_hit"，且目标对象可能出现在数组任意位置。

尝试的SQL语句返回空结果：

CREATE TABLE mytable (jsonstr JSON);

INSERT INTO mytable VALUES 
('{"f2": [{"name":"f21","value":"foo_21" }, 
          {"name":"hit","value":"foo_hit"}, 
          {"name":"f22","value":"foo_22" }]}');

SELECT JSON_EXTRACT(jsonstr,'$**.name') 
FROM mytable 
WHERE (JSON_EXTRACT(jsonstr,'$**.name')="hit");

问题原因 #

原SQL中JSON_EXTRACT(jsonstr,'$**.name')返回的是包含所有name值的JSON数组（如["f21", "hit", "f22"]），直接和字符串"hit"比较永远不相等，因此无结果返回。

方法一：使用JSON_TABLE（MySQL 8.0.4+推荐） #

将JSON数组转换为关系型表结构，再通过条件筛选目标值：

SELECT JSON_QUOTE(jt.value) AS target_value
FROM mytable,
     JSON_TABLE(
         jsonstr->'$.f2',
         '$[*]' COLUMNS (
             name VARCHAR(50) PATH '$.name',
             value VARCHAR(50) PATH '$.value'
         )
     ) AS jt
WHERE jt.name = 'hit';

• JSON_TABLE把f2数组拆分成每行包含name和value的记录
• JSON_QUOTE用于给结果添加双引号，满足预期格式要求
• 最终结果："foo_hit"

方法二：使用JSON_SEARCH结合JSON_EXTRACT #

先定位目标对象的路径，再提取对应的value：

SELECT JSON_EXTRACT(
    jsonstr,
    REPLACE(JSON_SEARCH(jsonstr, 'one', 'hit', NULL, '$**.name'), '.name', '.value')
) AS target_value
FROM mytable;

• JSON_SEARCH查找name等于hit的路径，返回"$.f2[1].name"
• REPLACE把路径中的.name替换为.value，得到"$.f2[1].value"
• JSON_EXTRACT根据修改后的路径提取值，结果自带双引号："foo_hit"

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