MySQL如何将存储ID的JSON数组转换为关联值组成的JSON数组

涉及表结构 #

• 表A（ID与账号映射表）
  • id：整数类型，关联唯一标识
  • account：字符串类型，对应账号名称
  • 示例数据：id=1 对应 account=kenvin，id=2 对应 account=charles
• 表B（业务主表）
  • id：业务主键
  • title：业务标题
  • target：业务关联目标字段
  • table_a_ids：JSON数组类型字段，存储关联表A的ID集合，示例值为[1,2]、[]、[2]

需求规则 #

查询表B数据时新增返回字段table_a_accounts，输出格式为JSON数组，需要满足：

• 数组元素顺序和table_a_ids内ID顺序完全一致
• 每个ID替换为表A中匹配到的对应账号值
• 空ID数组直接返回空数组[]
• 匹配示例：
  • table_a_ids = [1,2] 时返回 ["kenvin","charles"]
  • table_a_ids = [] 时返回 []
  • table_a_ids = [2] 时返回 ["charles"]

可直接复用的实现代码 #

MySQL 8.0+ 版本 #

核心逻辑是先通过下标遍历展开JSON数组，记录元素位置保证顺序，关联映射表拿到账号后再按原顺序聚合回JSON数组。

SELECT
    b.id,
    b.title,
    b.target,
    b.table_a_ids,
    CASE
        WHEN JSON_LENGTH(b.table_a_ids) = 0 THEN JSON_ARRAY()
        ELSE JSON_ARRAYAGG(a.account ORDER BY b.ele_index)
    END AS table_a_accounts
FROM
(
    SELECT
        id,
        title,
        target,
        table_a_ids,
        JSON_EXTRACT(table_a_ids, CONCAT('$[', seq.ele_index, ']')) AS map_id,
        seq.ele_index
    FROM table_b
    -- 序列表示例覆盖最大数组长度为10，可根据业务实际数组最大长度扩展
    JOIN (
        SELECT 0 AS ele_index UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9
    ) seq ON seq.ele_index < JSON_LENGTH(table_a_ids)
) b
LEFT JOIN table_a a ON a.id = b.map_id
GROUP BY b.id, b.title, b.target, b.table_a_ids;

PostgreSQL 版本 #

可以通过内置的JSON展开函数带序号返回的能力简化写法：

SELECT
    b.*,
    COALESCE(
        JSON_AGG(a.account ORDER BY map_info.ele_index) FILTER (WHERE a.account IS NOT NULL),
        JSON_ARRAY()
    ) AS table_a_accounts
FROM table_b b
LEFT JOIN LATERAL JSON_ARRAY_ELEMENTS_TEXT(b.table_a_ids) WITH ORDINALITY map_info(map_id, ele_index) ON true
LEFT JOIN table_a a ON a.id = map_info.map_id::INT
GROUP BY b.id;

注意点 #

• 展开数组时必须记录元素下标，聚合时按下标排序才能保证输出账号数组和原ID数组顺序一致
• 左连接映射表的写法会自动处理ID在表A中不存在的场景，对应位置会返回null，如果需要过滤无效ID可以将左连接改为内连接
• 空数组场景单独判断直接返回空JSON数组，避免聚合后返回null不符合预期

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