如何在MySQL中将JSON数组值拆分为单独行

原表结构 #

你当前使用的查询语句：

select JSON_EXTRACT(t.data, '$') as id from table1 t where t.id = 1; 

注：原语句中t.date应为笔误，修正为t.data。该语句返回完整数组，若改为'$[0]'仅能获取索引0的元素。

期望结果 #

针对MySQL 8.0及以上版本的方案 #

使用JSON_TABLE函数可以直接将JSON数组拆分为多行，这是最简洁高效的方式：

SELECT j.result
FROM table1 t
JOIN JSON_TABLE(
    t.data,
    '$[*]' COLUMNS(result VARCHAR(255) PATH '$')
) j
WHERE t.id = 1;

$[*]用于遍历数组中的所有元素，PATH '$'指定提取每个元素的值，通过JOIN关联原表与拆分后的结果集，再筛选id=1的记录即可得到目标输出。

兼容MySQL 5.x版本的方案 #

如果使用的是无JSON_TABLE的低版本MySQL，可以借助数字辅助表实现：

1. 先创建一个包含连续数字的辅助表（数字范围覆盖你的JSON数组最大长度）：

CREATE TABLE nums (n INT);
INSERT INTO nums VALUES (1), (2), (3); -- 根据实际数组长度调整

2. 执行拆分查询：

SELECT JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$[', n-1, ']'))) AS result
FROM table1 t
JOIN nums ON n <= JSON_LENGTH(t.data)
WHERE t.id = 1;

JSON_LENGTH(t.data)获取数组的元素个数，关联辅助表时只保留不超过数组长度的数字；CONCAT('$[', n-1, ']')动态生成每个元素的JSON路径；JSON_UNQUOTE用于去掉结果中的引号（如果不需要去除引号可省略该函数）。

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