MySQL中如何匹配JSON字段值并提取对应其他字段的值

需求说明 #

现有MySQL表persons存储了JSON类型的json字段，字段结构如下：

{
    "doe": [
        {
            "firstname": "john",
            "age": 30,
            "married_to": "jane"
        },
        {
            "firstname": "jane",
            "age": 28,
            "married_to": "john"
        }
    ]
}

需要提取doe数组中firstname值为jane的元素对应的age字段值。

原有方案问题 #

直接使用JSON_SEARCH全局搜索值jane会优先匹配到第一个元素的married_to字段，返回路径"$[0].married_to"，无法定位到目标元素，错误示例如下：

SELECT JSON_SEARCH(JSON_EXTRACT(json, '$.doe'), 'one', 'jane') FROM persons;

思路可行性说明 #

先查找符合条件的数组下标，再根据下标提取age字段的思路是完全可行的。

实现方案 #

方案1：指定搜索路径的JSON_SEARCH方案（兼容MySQL 5.7+） #

通过JSON_SEARCH的第五个参数指定搜索路径范围，限定仅在firstname字段下搜索值jane，拿到正确路径后替换得到age字段的路径再取值：

SELECT 
  JSON_EXTRACT(
    json,
    CONCAT(
      REPLACE(JSON_UNQUOTE(JSON_SEARCH(json, 'one', 'jane', NULL, '$.doe[*].firstname')), '.firstname', ''),
      '.age'
    )
  ) AS jane_age
FROM persons;

方案2：JSON_TABLE转结构化表方案（推荐，MySQL 8.0+） #

使用JSON_TABLE直接将JSON数组转为结构化临时表，筛选逻辑更直观，支持复杂多条件查询，代码可读性更高：

SELECT j.age
FROM 
  persons,
  JSON_TABLE(
    JSON_EXTRACT(persons.json, '$.doe'),
    '$[*]' COLUMNS (
      firstname VARCHAR(10) PATH '$.firstname',
      age INT PATH '$.age'
    )
  ) j
WHERE j.firstname = 'jane';

注：JSON_TABLE为MySQL 8.0新增函数，低于该版本无法使用

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