MySQL从JSON数组列提取匹配指定dcentro_id的topicName

数据库表tool_bbdd.workspace中topics列存储对象结构的JSON数组，每行数组内容独立。
数组内每个顶层对象包含三个字段：

• topicId：主题ID
• topicName：主题名称
• Dcentros：嵌套子数组，子数组内对象存储dcentro_id、dcentro_name两个字段

列值示例：
第一行topics值：

[
    {
        "topicId": "CON",
        "Dcentros": [
            {"dcentro_id": "9388TG", "dcentro_name": "9388TG"},
            {"dcentro_id": "9387OC", "dcentro_name": "9387OC"}
        ],
        "topicName": "CONFLUENCE"
    },
    {
        "topicId": "PMO",
        "Dcentros": [{"dcentro_id": "9387OC", "dcentro_name": "9387OC"}],
        "topicName": "PMO"
    },
    {
        "topicId": "TSM",
        "Dcentros": [{"dcentro_id": "9387CC", "dcentro_name": "9387CC"}],
        "topicName": "TSM"
    }
]

第二行topics值：

[
    {
        "topicId": "COS",
        "Dcentros": [
            {"dcentro_id": "9388TG", "dcentro_name": "9388TG"},
            {"dcentro_id": "9387OC", "dcentro_name": "9387OC"}
        ],
        "topicName": "CONNECTIONS"
    },
    {
        "topicId": "PTO",
        "Dcentros": [{"dcentro_id": "9387OC", "dcentro_name": "9387OC"}],
        "topicName": "PTO"
    },
    {
        "topicId": "TAM",
        "Dcentros": [{"dcentro_id": "9387CC", "dcentro_name": "9387CC"}],
        "topicName": "TAMIT"
    }
]

需求为：当顶层对象的Dcentros子数组中存在指定dcentro_id值时，提取对应对象的topicName。例如查询dcentro_id = 9387CC时，期望返回结果为["TSM", "TAMIT"]。

原有查询语句的问题：

SELECT  JSON_EXTRACT(topics, '$[*].topicName') as c_name  FROM tool_bbdd.workspace WHERE JSON_EXTRACT(JSON_EXTRACT(topics, '$[*].Dcentros[*].dcentro_id'), '$[*]') = '9387CC';

该语句仅做了行级匹配：只要整行的topics数组任意位置存在目标dcentro_id就返回整行，且SELECT阶段直接提取了整行所有顶层对象的topicName，没有做对象级别的筛选，因此会返回匹配行内的全部主题名，不符合需求。

MySQL 8.0及以上版本（推荐） #

使用JSON_TABLE函数将嵌套JSON数组拆成行级结构后筛选，逻辑最清晰性能最好。
如果需要全表所有匹配的topicName聚合为单个数组，使用如下语句：

SELECT
  JSON_ARRAYAGG(t.topic_name) AS c_name
FROM
  tool_bbdd.workspace w,
  JSON_TABLE(
    w.topics,
    '$[*]' COLUMNS(
      topic_name VARCHAR(100) PATH '$.topicName',
      NESTED PATH '$.Dcentros[*]' COLUMNS(
        dcentro_id VARCHAR(20) PATH '$.dcentro_id'
      )
    )
  ) t
WHERE t.dcentro_id = '9387CC';

语句逻辑：

1. JSON_TABLE将topics数组的每个顶层对象拆为独立行，同时嵌套拆分出每个对象下Dcentros数组的所有dcentro_id
2. WHERE条件直接筛选dcentro_id等于目标值的记录
3. 最后用JSON_ARRAYAGG将符合条件的topicName聚合成JSON数组返回

如果需要逐行返回每行匹配到的主题数组，可按表主键分组，示例如下：

SELECT
  w.id, -- 替换为表实际主键/需要返回的行字段
  JSON_ARRAYAGG(t.topic_name) AS match_topic_names
FROM
  tool_bbdd.workspace w,
  JSON_TABLE(
    w.topics,
    '$[*]' COLUMNS(
      topic_name VARCHAR(100) PATH '$.topicName',
      NESTED PATH '$.Dcentros[*]' COLUMNS(
        dcentro_id VARCHAR(20) PATH '$.dcentro_id'
      )
    )
  ) t
WHERE t.dcentro_id = '9387CC'
GROUP BY w.id;

MySQL 5.7兼容方案 #

MySQL 5.7不支持JSON_TABLE，可通过枚举数组下标+JSON_SEARCH实现，注意需要根据topics数组的实际最大长度扩展内联的数字序列：

SELECT
  JSON_EXTRACT(
    res.topics,
    GROUP_CONCAT(CONCAT('$[', res.idx, '].topicName'))
  ) AS c_name
FROM (
  SELECT
    w.topics,
    seq.idx
  FROM
    tool_bbdd.workspace w,
    -- 内联数字序列，覆盖数组可能的最大下标即可，示例覆盖长度0-19，可自行扩展
    (
        SELECT 0 idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19
    ) seq
  WHERE
    seq.idx < JSON_LENGTH(w.topics)
    AND JSON_SEARCH(
      JSON_EXTRACT(w.topics, CONCAT('$[', seq.idx, '].Dcentros[*].dcentro_id')),
      'one',
      '9387CC'
    ) IS NOT NULL
) res;

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