Remove array element by value in mysql json

Question

Is it possible to remove an element by its value (and not its index) in a json array in mysql? For example:

# ['new', 'orange']
update waitinglist SET new = JSON_REMOVE("orange", '$') WHERE id=2;
# now it will be ['new']

If not, is there a better way to store this, so I can remove elements as needed? Also, there would never be duplicates in the array.

score 12 · Accepted Answer · edited Sep 11 '22 at 08:40

12

If you know there are never duplicates in the array, you can use JSON_SEARCH to find the path to the value you want to delete, and then use JSON_REMOVE to remove it. Note that you need to check that JSON_SEARCH actually finds a value, otherwise JSON_REMOVE will nullify the entire field:

UPDATE waitinglist 
SET new = JSON_REMOVE(new, JSON_UNQUOTE(JSON_SEARCH(new, 'one', 'orange')))
WHERE JSON_SEARCH(new, 'one', 'orange') IS NOT NULL

or

UPDATE waitinglist SET new = IFNULL(JSON_REMOVE(new, JSON_UNQUOTE(JSON_SEARCH(new, 'one', 'orange'))),new)

I've made a small demo on dbfiddle.

Note you have to use JSON_UNQUOTE on the response from JSON_SEARCH to make it a valid path for JSON_REMOVE.

edited Sep 11 '22 at 08:40

Deepanshu Suman

13
1
3

answered Dec 08 '18 at 00:06

Nick

138,499
22
57
95

what if the element doesn't exist though? The above would nullify the entire field. – David542 Dec 08 '18 at 00:49
@David542 good point! I've edited my answer with a correction. – Nick Dec 08 '18 at 00:53
Data truncated for column – Iman Marashi May 25 '20 at 18:48
@Nick i use this query in php: `Update question set $colName=IF(JSON_CONTAINS($colName, '$userId'), JSON_REMOVE($colName, '$.\"$userId\"'),JSON_ARRAY_APPEND($colName, '$', ?)) where question_id='$questionId'` And get this Error: `Data truncated for column` – Iman Marashi May 26 '20 at 10:56
@ImanMarashi that's not the same code as in this answer. I would suggest that you ask a new question with your code, making sure you include some sample data which demonstrates the problem. – Nick May 26 '20 at 11:55

score 1 · Answer 2 · answered Jul 27 '21 at 16:43

Here a bit different approach, but it allows to remove multiple value from the json array at once. I'm using subquery to get the correct array values and then just updating the json field. So in your case the sql query will look like this:

UPDATE waitinglist w
SET w.new =
        (
            SELECT JSON_ARRAYAGG(new)
            FROM JSON_TABLE(w.new, '$[*]' COLUMNS (new VARCHAR(255) PATH '$')) AS list
            WHERE list.new NOT IN ('orange')
        )
WHERE w.id = 2;

The values you want to remove from the json array must be specified in the NOT IN clause in the subquery.

Remove array element by value in mysql json

2 Answers2

Linked