MySQL中如何拼接JSON数组内的水果ID值？

问题原因 #

你用JSON_VALUE(col, '$.fruits[*].id')返回NULL的核心原因是：JSON_VALUE()仅能提取单个标量值，而$.fruits[*].id会返回一个JSON数组（比如[1,3]），不符合该函数的返回要求，因此返回NULL。

解决方案（MySQL 8.0+ 推荐） #

利用JSON_TABLE将JSON数组拆分为关系型行数据，再通过GROUP_CONCAT拼接ID：

SELECT 
  GROUP_CONCAT(j.id SEPARATOR ',') AS ids
FROM 
  your_table
JOIN JSON_TABLE(
  your_table.col,
  '$.fruits[*]' COLUMNS (
    id INT PATH '$.id'
  )
) AS j
GROUP BY your_table.id; -- 替换为你的表主键/唯一标识列，确保按原表每行分组

说明 #

1. JSON_TABLE：将JSON数组$.fruits中的每个对象拆分为一行，提取其中的id字段作为单独列。
2. GROUP_CONCAT：将同一原表行对应的所有拆分出的id，用逗号拼接成字符串。
3. 如果你的表只有一行数据，可以省略GROUP BY子句，直接执行即可得到1,3。

兼容MySQL 5.7的替代方案（无JSON_TABLE） #

如果无法升级到8.0，可通过JSON_EXTRACT结合字符串处理函数实现：

SELECT 
  TRIM(BOTH '[]' FROM REPLACE(JSON_EXTRACT(col, '$.fruits[*].id'), ', ', ',')) AS ids
FROM your_table;

该方法通过JSON_EXTRACT获取数组字符串[1, 3]，再替换空格、去掉方括号得到1,3，但注意如果JSON数组元素有空格或特殊字符可能失效，仅适合简单场景。

注意事项 #

• 如果存在id为NULL的元素，GROUP_CONCAT会自动忽略，若需保留可改为GROUP_CONCAT(IFNULL(j.id, '') SEPARATOR ',')。
• GROUP_CONCAT默认有1024字符长度限制，若需更长结果，可临时调整：SET SESSION group_concat_max_len = 1000000;

内容的提问来源于stack exchange，提问作者이준서