MySQL中LEFT JOIN关联JSON数组字段并GROUP_CONCAT全部匹配项的问题

你现在的问题根源在于只提取了JSON数组的第一个元素（$[0]），所以只能关联到单条记录。要获取所有匹配的关联记录，需要先把JSON数组拆分成独立的行，再进行关联和聚合操作。

方法一：MySQL 8.0+ 推荐方案（使用JSON_TABLE） #

MySQL 8.0及以上版本提供了JSON_TABLE函数，可以直接将JSON数组转换为关系型临时表，这是最简洁高效的处理方式：

SELECT 
    ppt.id,
    ppt.pay_name,
    ppt.configuration,
    ppt.pay_function_name,
    GROUP_CONCAT(pt.MODE SEPARATOR ', ') AS mode_list
FROM pay_payment_type ppt
LEFT JOIN JSON_TABLE(
    ppt.pay_type_fk,
    '$[*]' COLUMNS(pay_type_id BIGINT PATH '$')
) AS jt ON TRUE
LEFT JOIN pay_type pt ON jt.pay_type_id = pt.id
GROUP BY ppt.id, ppt.pay_name, ppt.configuration, ppt.pay_function_name;

代码说明： #

1. JSON_TABLE(ppt.pay_type_fk, '$[*]' COLUMNS(pay_type_id BIGINT PATH '$')) AS jt：把pay_type_fk数组里的每个元素拆成单独的行，生成临时表jt，其中pay_type_id对应数组中的每个ID值。
2. 通过临时表jt关联pay_type表，就能匹配到数组中所有对应的记录。
3. GROUP_CONCAT会把所有匹配的MODE字段拼接成字符串，SEPARATOR可以自定义分隔符（默认是逗号）。

方法二：MySQL 5.7 兼容方案（无JSON_TABLE） #

如果你的MySQL版本是5.7，没有JSON_TABLE函数，可以借助数字辅助表来拆分数组：

首先创建一个包含连续数字的临时辅助表（数字范围覆盖你的JSON数组最大长度即可）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),...,(100); -- 根据实际数组长度调整数字数量

然后用这个表拆分数组并关联查询：

SELECT 
    ppt.id,
    ppt.pay_name,
    ppt.configuration,
    ppt.pay_function_name,
    GROUP_CONCAT(pt.MODE SEPARATOR ', ') AS mode_list
FROM pay_payment_type ppt
LEFT JOIN nums n ON n.n <= JSON_LENGTH(ppt.pay_type_fk)
LEFT JOIN pay_type pt ON pt.id = CAST(JSON_EXTRACT(ppt.pay_type_fk, CONCAT('$[', n.n-1, ']')) AS UNSIGNED)
GROUP BY ppt.id, ppt.pay_name, ppt.configuration, ppt.pay_function_name;

代码说明： #

1. JSON_LENGTH(ppt.pay_type_fk)获取数组的长度，确保只遍历数组内的有效元素。
2. CONCAT('$[', n.n-1, ']')生成JSON数组的索引路径（JSON数组从0开始计数），逐个提取数组中的元素。
3. 最后通过GROUP_CONCAT拼接所有匹配的MODE值。

注意：这种方法的效率比JSON_TABLE低，仅适合版本受限的场景。

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