Search code examples
mysqlmysql-json

Remove array element by value in mysql json

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.

Solution

  • 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.