MySQL一行一行展开 JSON 数据中的数组，将每个元素都展开成一行

假如数据为以下格式：

{
    "result":[
        {
            "chatId":"wrKXPECAAA25IIvW6A-G6eUhT-g1bLkg",
            "name":"42HOW-小鹏 G6 兴趣群",
            "userJoinCount":249
        },
        {
            "chatId":"wrKXPECAAA25IIvW6A-G6eUhT-g1bLkg",
            "name":"42HOW-小鹏 G6 兴趣群",
            "userJoinCount":249
        }
    ]
}

sql 第一种写法

SELECT 
       JSON_EXTRACT(`value`, '$.result[0].name') AS name,
       JSON_EXTRACT(`value`, '$.result[0].userJoinCount') AS userJoinCount
FROM configs
WHERE id=1
UNION ALL
SELECT 
       JSON_EXTRACT(`value`, '$.result[1].name') AS name,
       JSON_EXTRACT(`value`, '$.result[1].userJoinCount') AS userJoinCount
FROM configs
WHERE id=1

sql 第二种写法

SELECT chatId, name, userJoinCount
FROM configs, 
  JSON_TABLE(value, '$.result[*]' COLUMNS(
    chatId VARCHAR(50) PATH '$.chatId',
    name VARCHAR(100) PATH '$.name',
    userJoinCount INT PATH '$.userJoinCount'
  )) as result
WHERE id =1
ORDER BY userJoinCount DESC

• 这个语句将 configs 表与 JSON_TABLE 的结果进行笛卡尔积，然后根据 WHERE 子句中的条件筛选出所需的行。JSON_TABLE 语句中的 $.result[*] 表示解析 JSON 字段中的 result 数组，并将其展开成多行，每行对应一个元素。然后使用 COLUMNS 子句指定要提取的字段和它们的类型和路径。最后，在主查询中，使用 chatId、name 和 userJoinCount 列来表示展开后的字段。