MySQL分组时合并JSON数组并去重的实现方法

要实现分组合并JSON数组并去重，直接用JSON_ARRAYAGG会生成嵌套数组，无法满足需求，需先将数组拆分为单行数据，去重后再重新聚合：

完整SQL语句 #

SELECT 
    source_account_id,
    JSON_ARRAYAGG(DISTINCT target_account_id) AS target_account_ids
FROM (
    -- 将JSON数组拆分为单行记录
    SELECT 
        t.source_account_id,
        j.target_account_id
    FROM transaction t
    JOIN JSON_TABLE(
        t.target_account_ids,
        '$[*]' COLUMNS(target_account_id VARCHAR(32) PATH '$')
    ) j
) AS flattened_data
GROUP BY source_account_id;

步骤说明 #

1. 拆分JSON数组：内层查询通过JSON_TABLE函数，把每条记录里的target_account_ids数组拆分成独立的行，将数组元素转为普通字符串列target_account_id。
2. 去重并聚合：外层查询按source_account_id分组，使用JSON_ARRAYAGG(DISTINCT ...)对去重后的账户ID重新组合成单一JSON数组。

版本要求 #

JSON_TABLE是MySQL 8.0及以上版本提供的函数，低版本MySQL无法使用该方案，需改用自定义函数拆分数组等其他字符串处理方式。

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