json_search and json_remove in mysql

Question

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'";

How do you uniquely identify the json object you want to remove from the array you have? — lemon, Oct 16 '22 at 16:34
I don't have a unique ID that I can identify, but I want it to be deleted if the contents of json are the same. — JiNyeok_S, Oct 16 '22 at 20:02

score 2 · Accepted Answer · answered Oct 16 '22 at 16:42

2

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)

answered Oct 16 '22 at 16:42

Akina

39,301
5
14
25

I have a separate column of ID, can I apply that too? – JiNyeok_S Oct 16 '22 at 21:19
@JiNyeok_S I cannot understand what you mean. – Akina Oct 16 '22 at 21:20
Can i make json_table by taking Row's notice with ID column of 'test2'? – JiNyeok_S Oct 16 '22 at 21:27
@JiNyeok_S `test2` is a table which is not mentioned in your original post? if so then this is separate question, and you'd create another topic. – Akina Oct 16 '22 at 21:34
I'm sorry. Thank you for your help. – JiNyeok_S Oct 16 '22 at 21:37

json_search and json_remove in mysql

1 Answers1