json_search and json_remove in mysql-CodePudding

notice
[
 {
   "date": "2022. 10. 16.", 
   "type": 3, 
   "title": "friend", 
   "content": "JJ friend", 
   "parameter": "test"
 }, 
 {
   "date": "2022. 10. 16.", 
   "type": 3, 
   "title": "friend", 
   "content": "testtest friend", 
   "parameter": "test1"
  }
]

I wanna search and remove in json {"date": "2022. 10. 16.","type": 3,"title": "friend","content": "testtest friend","parameter": "test1"} where id = 'test2'

There's a separate column for the ID.

update UserTable set notice = json_remove(notice, json_search(notice, 'one', 
'{"date": "2022. 10. 16.","type": 3,"title": "friend","content": "testtest friend","parameter": "test1"}')) where id = 'test2'";

CodePudding user response：

SELECT JSON_ARRAYAGG(jsonvalue)
FROM src_table
CROSS JOIN JSON_TABLE(src_table.notice,
                      '$[*]' COLUMNS (jsonvalue JSON PATH '$')) jsontable
WHERE jsonvalue <> CAST('{"date": "2022. 10. 16.","type": 3,"title": "friend","content": "testtest friend","parameter": "test1"}' AS JSON)