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.

Answer 1

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.

Answer 2

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.