MySQL将JSON字典数组拆分为行并提取user_id值

需求：现有一张表，其中users列存储由字典组成的JSON数组，需提取每个字典中的id值并每行展示一个；若该列为NULL或空数组，则返回NULL，实现类似Python Pandas的explode方法效果（数组长度不固定）。

原始表结构及数据 #

期望结果 #

方案1：PostgreSQL 实现 #

PostgreSQL原生支持JSON/JSONB类型，通过json_array_elements（或jsonb_array_elements）展开数组，结合COALESCE处理空值：

SELECT
    t.id,
    COALESCE(j.user_id, NULL) AS users
FROM original_table t
LEFT JOIN LATERAL (
    SELECT (json_array_elements(t.users)->>'id')::INT AS user_id
) j ON t.users <> '[]'::json AND t.users IS NOT NULL

说明：

• json_array_elements(t.users)将JSON数组拆分为每行一个JSON对象
• ->>'id'提取对象中的id字段为文本类型，再转为INT
• LEFT JOIN LATERAL保证原表所有行被保留，空数组或NULL的行对应返回NULL
• 若使用jsonb类型，替换json_array_elements为jsonb_array_elements即可

方案2：MySQL 8.0+ 实现 #

MySQL 8.0及以上支持JSON_TABLE函数，可直接将JSON数组转换为关系表：

SELECT
    t.id,
    COALESCE(j.user_id, NULL) AS users
FROM original_table t
LEFT JOIN JSON_TABLE(
    COALESCE(t.users, '[]'),
    '$[*]' COLUMNS(
        user_id INT PATH '$.id'
    )
) j ON JSON_LENGTH(t.users) > 0

说明：

• JSON_TABLE遍历数组所有元素（$[*]），提取每个对象的id字段
• COALESCE(t.users, '[]')处理NULL值，避免JSON_TABLE报错
• LEFT JOIN确保空数组或NULL的行保留并返回NULL

方案3：MySQL 5.x 实现（无JSON_TABLE） #

MySQL 5.x无JSON_TABLE，可借助数字辅助表模拟展开：

先创建数字辅助表（覆盖数组最大长度）：

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5); -- 按需添加更多数字

执行查询：

SELECT
    t.id,
    COALESCE(JSON_EXTRACT(t.users, CONCAT('$[', n-1, '].id')), NULL) AS users
FROM original_table t
LEFT JOIN numbers n ON n <= JSON_LENGTH(t.users)
WHERE JSON_LENGTH(t.users) > 0 OR t.users IS NULL
GROUP BY t.id, n
ORDER BY t.id, users

说明：

• JSON_LENGTH(t.users)获取数组长度，数字表的n匹配数组索引（JSON数组从0开始，用n-1）
• JSON_EXTRACT提取对应索引对象的id字段
• 空数组或NULL的行因JSON_LENGTH为0，LEFT JOIN无匹配，最终返回NULL

内容的提问来源于stack exchange，提问作者Emilio Carrasco Moreno