MySQL查询JSON值：如何无需硬编码索引关联表获取对应名称？

针对JSON数组长度不固定、无法硬编码索引的问题，推荐使用MySQL 8.0及以上版本支持的JSON_TABLE函数来动态解析数组，配合分组拼接实现需求，无需硬编码索引。

最优解决方案（MySQL 8.0+） #

SELECT 
    pi.player_id,
    GROUP_CONCAT(cc.name SEPARATOR ', ') AS character_class_name
FROM 
    player_inventories pi
JOIN 
    JSON_TABLE(
        pi.character_class,
        '$[*]' COLUMNS (class_id VARCHAR(10) PATH '$')
    ) jt ON TRUE
JOIN 
    character_classes cc ON jt.class_id = cc.id
GROUP BY 
    pi.player_id;

代码说明：

1. JSON_TABLE解析数组：将player_inventories表中每个character_class的JSON数组拆分为多行数据，每行对应一个职业ID（class_id）。
2. 关联职业表：通过拆分出的class_id关联character_classes表，获取对应的职业名称。
3. 分组拼接结果：按player_id分组，用GROUP_CONCAT将多个职业名称拼接为逗号分隔的字符串，自动适配数组的任意长度。

兼容MySQL 5.x的替代方案 #

如果你的MySQL版本低于8.0，不支持JSON_TABLE，可以用递归CTE生成数组索引，逐个提取元素关联：

WITH RECURSIVE cte AS (
    SELECT 
        player_id,
        character_class,
        0 AS idx,
        JSON_LENGTH(character_class) AS arr_len
    FROM 
        player_inventories
    UNION ALL
    SELECT 
        player_id,
        character_class,
        idx + 1,
        arr_len
    FROM 
        cte
    WHERE 
        idx + 1 < arr_len
)
SELECT 
    cte.player_id,
    GROUP_CONCAT(cc.name SEPARATOR ', ') AS character_class_name
FROM 
    cte
JOIN 
    character_classes cc ON cc.id = JSON_UNQUOTE(JSON_EXTRACT(cte.character_class, CONCAT('$[', idx, ']')))
GROUP BY 
    cte.player_id;

这个方案通过递归生成数组的每个索引位置，逐个提取职业ID并关联，最后拼接结果，缺点是性能不如JSON_TABLE，适合无法升级版本的场景。

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