MySQL 5.7.22中如何替换JSON字段ID为对应名称

表结构与测试数据 #

Schema (MySQL v5.7.22)：

CREATE TABLE `config_remap` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200),
  `addons` json,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_name` varchar(500) DEFAULT NULL,
  `requested_remap_id` int(11) DEFAULT NULL,
  `requested_addons` json, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `config_remap` (`id`, `name`, `addons`) 
VALUES(1, 'Remap 1', 
'[{"id": 0,"name": "Addon 0"},
  {"id": 1,"name": "Addon 1"},
  {"id": 2,"name": "Addon 2"}
]');

INSERT INTO `products` (`id`, `product_name`, `requested_remap_id`, `requested_addons`) 
VALUES (1, 'Audi A4', 1, '["0", "2"]');

当前查询及结果 #

现有查询语句：

select products.id, products.product_name, products.requested_addons, config_remap.name as requested_addon
from products
    left join config_remap on config_remap.id = products.requested_remap_id

返回结果：

需求 #

修改查询，将requested_addons中的ID替换为config_remap.addons中对应的名称，期望结果：

解决方案 #

由于MySQL 5.7不支持JSON_TABLE函数，需要通过拆分JSON、关联匹配再聚合的方式实现，以下是可行的查询语句：

SELECT
    p.id,
    p.product_name,
    JSON_ARRAYAGG(c.addon_name) AS requested_addons,
    cr.name AS requested_addon
FROM products p
LEFT JOIN config_remap cr ON cr.id = p.requested_remap_id
-- 拆分config_remap.addons中的每个addon项
LEFT JOIN (
    SELECT 
        cr_inner.id,
        JSON_UNQUOTE(JSON_EXTRACT(cr_inner.addons, CONCAT('$[', nums.idx, '].id'))) AS addon_id,
        JSON_UNQUOTE(JSON_EXTRACT(cr_inner.addons, CONCAT('$[', nums.idx, '].name'))) AS addon_name
    FROM config_remap cr_inner
    -- 生成数字序列，覆盖addons可能的元素数量，可按需扩展
    CROSS JOIN (
        SELECT 0 AS idx 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
    ) nums
    -- 过滤掉不存在的数组索引
    WHERE JSON_EXTRACT(cr_inner.addons, CONCAT('$[', nums.idx, '].id')) IS NOT NULL
) c ON cr.id = c.id 
    -- 匹配requested_addons中的ID
    AND FIND_IN_SET(c.addon_id, REPLACE(REPLACE(JSON_UNQUOTE(p.requested_addons), '[', ''), ']', '')) > 0
GROUP BY p.id, p.product_name, cr.name;

关键说明 #

1. 子查询通过数字序列拆分config_remap.addons数组，提取每个addon的ID和名称；
2. 使用FIND_IN_SET匹配products.requested_addons中的ID，避免LIKE可能带来的误匹配；
3. 最后用JSON_ARRAYAGG将匹配到的名称聚合为JSON数组；
4. 如果addons中的元素数量超过8个，需要在数字序列中增加更多的SELECT n语句。

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