MySQL中如何从JSON数组中移除指定元素且不删除整个数组

错误原因说明 #

JSON_REMOVE需要传入目标元素的精确JSON路径（如$[下标]），如果未正确匹配到有效路径、或匹配失败后未做空值判断，会导致字段被更新为NULL，表现为整个数组被清空。

方案1：MySQL 8.0+ 通用方案（推荐） #

通过JSON_SEARCH先定位目标元素的路径，结合空值判断避免清空数组：

UPDATE users 
SET follows = IFNULL(
  JSON_REMOVE(
    follows, 
    JSON_UNQUOTE(JSON_SEARCH(follows, 'one', 'followerToBeRemoved'))
  ),
  follows
)
WHERE username = "user"
-- 可选添加以下条件，仅操作包含目标关注者的行，提升性能
AND JSON_CONTAINS(follows, '"followerToBeRemoved"')

参数说明：

• JSON_SEARCH(follows, 'one', 'followerToBeRemoved')：查找数组中第一个匹配目标值的路径，返回带双引号的路径字符串（如"$[2]"）
• JSON_UNQUOTE：去除路径字符串的双引号，转为JSON_REMOVE可识别的路径格式
• IFNULL(..., follows)：如果未匹配到目标元素（JSON_SEARCH返回NULL），则保留原数组，避免字段被置为NULL
• 如果需要删除数组中所有匹配的重复值，将JSON_SEARCH的第二个参数'one'修改为'all'即可。

方案2：兼容低版本MySQL/批量过滤场景 #

如果数据库版本不支持JSON_SEARCH，可以通过JSON转表过滤后重新聚合的方式实现：

UPDATE users u
JOIN (
  SELECT 
    username,
    JSON_ARRAYAGG(follow_item) AS new_follows
  FROM users,
       JSON_TABLE(
         follows,
         '$[*]' COLUMNS (follow_item VARCHAR(255) PATH '$')
       ) t
  WHERE username = "user"
    AND follow_item != 'followerToBeRemoved'
  GROUP BY username
) t ON u.username = t.username
SET u.follows = t.new_follows

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