如何用MySQL拆分JSON数组值为独立行并提取关联数据？

问题说明 #

现有一个MySQL表包含两列：

• Customer id
• json_data（存储特定嵌套结构的JSON对象）

JSON数据结构示例 #

{
  "nameValuePairs": {
    "CONTACTS": {
      "nameValuePairs": {
        "contacts": {
          "values": [
            {
              "nameValuePairs": {
                "contact_id": "1",
                "contact_phoneNumber": "080000016",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Huawei Customer Service",
                "contact_last_name": "Huawei Customer Service",
                "contact_title": "Huawei Customer Service",
                "contact_email": "mobile.pk@huawei.com"
              }
            },
            {
              "nameValuePairs": {
                "contact_id": "2",
                "contact_phoneNumber": "15",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Police Helpline",
                "contact_last_name": "Police Helpline",
                "contact_title": "Police Helpline"
              }
            },
            {
              "nameValuePairs": {
                "contact_id": "3",
                "contact_phoneNumber": "16",
                "contact_phoneNumberCategory": "Mobile",
                "contact_firstName": "Fire Brigade Helpline",
                "contact_last_name": "Fire Brigade Helpline",
                "contact_title": "Fire Brigade Helpline"
              }
            }
          ]
        }
      }
    }
  }
}

期望输出 #

需要将每个contact_title与对应的Customer id单独成行，示例结果如下：

当前查询的问题 #

使用单条JSON_EXTRACT只能提取数组整体，导致每个Customer id仅返回一行，所有contact_title合并为数组：

JSON_EXTRACT(json_data, '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values[0].nameValuePairs.contact_title') AS "Contact name"

得到的结果不符合需求：

解决方案 #

使用MySQL 8.0及以上版本支持的JSON_TABLE函数，将JSON数组拆分为独立行：

SELECT 
    t.`Customer id`,
    jt.contact_title
FROM 
    your_table_name t,
    JSON_TABLE(
        -- 提取联系人数组
        JSON_EXTRACT(t.json_data, '$.nameValuePairs.CONTACTS.nameValuePairs.contacts.values'),
        -- 遍历数组每个元素
        '$[*]' COLUMNS (
            -- 提取每个元素中的contact_title字段
            contact_title VARCHAR(255) PATH '$.nameValuePairs.contact_title'
        )
    ) jt
-- 可选：过滤需要保留的contact_title值，匹配期望结果
WHERE 
    jt.contact_title IN ('Huawei Customer Service', 'Police Helpline');

关键说明 #

1. JSON_EXTRACT先定位到JSON中的联系人数组节点
2. JSON_TABLE将数组的每个元素转换为一行记录，通过PATH指定要提取的字段
3. 主表与JSON_TABLE结果做交叉连接，实现每个联系人对应原表的Customer id单独成行
4. 若需筛选特定联系人，可通过WHERE子句添加过滤条件

注意：若MySQL版本低于8.0，JSON_TABLE不可用，可考虑使用数字辅助表结合索引遍历数组，但扩展性较差，建议优先升级版本。

内容的提问来源于stack exchange，提问作者Muhammad Abdullah Khan