如何在MySQL的JSON数组列中移除指定元素？

问题原因 #

你的SQL执行后group_in变为null，核心问题是：

• group_in存储的是数字类型的JSON数组，但JSON_SEARCH默认会将传入的搜索值1当作字符串匹配数组中的数字元素，导致找不到匹配项返回null。
• JSON_REMOVE接收null作为路径参数时，会直接返回null，最终将字段值覆盖为null。

解决方案 #

方案1：修正JSON_SEARCH的类型匹配 #

通过CAST(1 AS JSON)将搜索值转为JSON数字类型，让JSON_SEARCH能正确匹配数组元素：

UPDATE users 
SET group_in = JSON_REMOVE(group_in, JSON_UNQUOTE(JSON_SEARCH(group_in, 'one', CAST(1 AS JSON))))
WHERE id = 1;

注：若需删除数组中所有匹配的1，可将'one'改为'all'，但需额外处理多路径拼接逻辑，更推荐方案2。

方案2：用JSON_TABLE重构数组（MySQL 8.0+） #

该方法更灵活，适合多元素删除或复杂过滤场景：

UPDATE users u
JOIN (
    SELECT id, JSON_ARRAYAGG(value) AS new_group_in
    FROM users, JSON_TABLE(group_in, '$[*]' COLUMNS(value INT PATH '$')) jt
    WHERE id = 1 AND value != 1
    GROUP BY id
) j ON u.id = j.id
SET u.group_in = j.new_group_in
WHERE u.id = 1;

原理：将JSON数组展开为行数据，过滤掉目标元素后，重新聚合为JSON数组。

验证结果 #

执行任一方案后，数据将符合预期：

id | group_in
1  | [2,3]
2  | [1,3]

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