如何在MySQL中用json_search和json_remove删除指定ID的JSON数组元素

原语句的问题 #

你写的这条SQL大概率无法生效，原因有两个：

1. 字符串精确匹配的局限性：json_search是按文本字符串精确匹配的，但JSON对象的键顺序、空格、换行格式不影响语义，却会改变字符串内容。比如你传入的目标对象是紧凑的{"date": "2022. 10. 16.","type": 3,...}，但数据表中存储的对象是带换行和空格的格式，两者文本完全不同，json_search会返回NULL，导致json_remove无法执行删除操作。
2. JSON对象匹配逻辑问题：json_search默认针对字符串、数字等基础类型值的查找，直接用它匹配整个JSON对象，即使格式完全一致，也可能因为JSON类型的匹配规则导致失败。

正确的SQL写法 #

推荐使用JSON_TABLE将JSON数组拆分为行，过滤掉目标对象后重新聚合为数组，这种方式不依赖JSON的存储格式，只匹配键值对内容：

UPDATE UserTable
SET notice = (
  SELECT JSON_ARRAYAGG(j.obj)
  FROM JSON_TABLE(
    notice,
    '$[*]' COLUMNS(obj JSON PATH '$')
  ) j
  WHERE NOT (
    j.obj->>'$.date' = '2022. 10. 16.'
    AND j.obj->>'$.type' = 3
    AND j.obj->>'$.title' = 'friend'
    AND j.obj->>'$.content' = 'testtest friend'
    AND j.obj->>'$.parameter' = 'test1'
  )
)
WHERE id = 'test2';

代码说明 #

• JSON_TABLE(notice, '$[*]' COLUMNS(obj JSON PATH '$'))：把notice数组中的每个JSON对象拆成单独的行，每行对应一个对象。
• WHERE NOT (...)：过滤掉完全匹配目标键值对的对象。
• JSON_ARRAYAGG(j.obj)：把剩下的对象重新组合成JSON数组，替换原notice字段的值。

这个方法适用于MySQL 8.0及以上版本，是处理JSON数组元素删除的可靠方式。

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