如何在MySQL 5.7中实现PostgreSQL json_each的JSON行提取功能

由于MySQL 5.7不支持json_table()或json_each()这类JSON行转列函数，我们可以通过数字辅助表+JSON内置函数的组合实现相同效果，以下是具体步骤：

1. 创建测试表并插入数据 #

先还原PostgreSQL示例中的测试环境：

CREATE TABLE test (id INT, data JSON);

INSERT INTO test VALUES 
(1, '{"1":[0,0,1], "2":[0,1,0]}'), 
(2, '{"2":[0,0,1], "3":[0,1,0], "4":[0,0,0]}');

2. 准备数字辅助表 #

我们需要一个连续数字表来遍历JSON对象中的每个键。可以创建临时表（适合频繁使用）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
-- 按需扩展数字范围，覆盖业务中最多的code数量即可

如果不想创建临时表，也可以用子查询动态生成数字（适合小数据量场景）：

SELECT @row := @row + 1 AS n FROM 
(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t1,
(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t2,
(SELECT @row := 0) t0

3. 编写拆分查询语句 #

利用JSON_KEYS()获取JSON所有键，结合数字表逐个提取键值对，并拆分数组中的cur/min/max值：

SELECT 
    t.id,
    -- 提取JSON键并转为整数类型的code
    CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data), CONCAT('$[', n.n-1, ']'))) AS UNSIGNED) AS code,
    -- 提取对应code数组中的current值
    JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data), CONCAT('$[', n.n-1, ']'))), '[0]'))) AS cur_val,
    -- 提取对应code数组中的min值
    JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data), CONCAT('$[', n.n-1, ']'))), '[1]'))) AS min_val,
    -- 提取对应code数组中的max值
    JSON_UNQUOTE(JSON_EXTRACT(t.data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.data), CONCAT('$[', n.n-1, ']'))), '[2]'))) AS max_val
FROM test t
-- 关联数字表，只取不超过JSON键数量的数字，避免多余空行
JOIN nums n ON n.n <= JSON_LENGTH(JSON_KEYS(t.data))
ORDER BY t.id, code;

如果用动态数字子查询，替换JOIN nums n为：

JOIN (
    SELECT @row := @row + 1 AS n FROM 
    (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t1,
    (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t2,
    (SELECT @row := 0) t0
) n ON n.n <= JSON_LENGTH(JSON_KEYS(t.data))

执行结果 #

最终查询会输出和PostgreSQL示例完全一致的结果：

id | code | cur_val | min_val | max_val
---|------|---------|---------|---------
1  | 1    | 0       | 0       | 1
1  | 2    | 0       | 1       | 0
2  | 2    | 0       | 0       | 1
2  | 3    | 0       | 1       | 0
2  | 4    | 0       | 0       | 0

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