如何在MySQL中合并多行的JSON数组值？

问题背景 #

现有表格数据如下：

需要将所有Access字段的数组合并为一个扁平且去重的数组，预期结果：

["1","2","3"]

使用SELECT JSON_ARRAYAGG(Access) FROM your_table;得到的是嵌套数组，不符合需求：

[["1"],["2","3"],["3"]]

可行解决方案 #

方案1：用JSON_TABLE解析（MySQL 8.0+推荐） #

通过JSON_TABLE把每个Access数组里的元素拆成单独行，再聚合去重：

SELECT JSON_ARRAYAGG(DISTINCT access_item) AS merged_access
FROM your_table,
     JSON_TABLE(Access, '$[*]' COLUMNS (access_item VARCHAR(255) PATH '$')) AS jt;

这个方法直接解析JSON数组，逻辑清晰效率高，是最优选择。

方案2：字符串处理兼容低版本 #

如果你的MySQL版本低于8.0，不支持JSON_TABLE，可以用字符串拆分的方式实现：

SELECT JSON_ARRAY(
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(Access, '[', ''), ']', ''), ', ', numbers.n), ', ', -1)
) AS merged_access
FROM your_table
JOIN (
  SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 -- 这里的数字要覆盖数组最多元素数
) numbers
ON CHAR_LENGTH(Access) - CHAR_LENGTH(REPLACE(Access, ',', '')) >= numbers.n - 1;

注意：需要根据实际数组的最大元素个数调整numbers里的数字数量。

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