How to remove a number from MySQL's JSON array?

Question

If I have a MySQL table with a JSON column called numbers and a record that has [1, 2, 3] in that column (array of integers), how do I update that record to remove the 2 (so it becomes [1, 3])?

Answer 1

I was searching for an answer my self and came to this question, not wanting to use objects I continued the search. But I found a solution, you need to use a combination of json_remove and json_search

The following removes the value 1 from the table tbl and the column numbers

UPDATE tbl
SET numbers = JSON_REMOVE(
  numbers, replace(json_search(numbers, 'one', 1), '"', '')
)
WHERE json_search(numbers, 'one', 1) IS NOT NULL

1. json_search returns the path of where the value is, ie. "$[0]"
2. replace remove the " otherwise an error will occur with json_remove
3. json_remove will remove the path from the json_search result

Et voila, your value is removed.

Note: this assumes no duplicate values