As of writing this, MySQL has no dedicated JSON function to allow you to remove a JSON array element by its value. You have to use whatever functions are available to come up with a custom expression to achieve your goal. Depending on the requirements, you might even want to create a custom function.
More specifically, if the requirement is to remove the first occurrence of a value, then one way to do it is like this:
SET @value := 'b';
SELECT
j.j AS before_remove
, JSON_REMOVE(
j.j
, JSON_UNQUOTE(
JSON_SEARCH(
j.j
, 'one'
, @value
)
)
) AS after_remove
FROM
(SELECT '["a", "b", "c", "b"]') AS j (j)
;
Returns:
before_remove |
after_remove |
["a", "b", "c", "b"] |
["a", "c", "b"] |
JSON_SEARCH
returns a quoted path to the value found ("$[1]"
in this case), JSON_UNQUOTE
removes the quotes (yielding $[1]
), following which the path can be fed to JSON_REMOVE
.
If you want to remove an arbitrary occurrence of a value, the expression becomes somewhat more complex:
SET @value := 'b';
SET @occurrence := 2;
SELECT
j.j AS before_remove
, JSON_REMOVE(
j.j
, JSON_UNQUOTE(
JSON_EXTRACT(
JSON_SEARCH(
j.j
, 'all'
, @value
)
, CONCAT('$[', @occurrence - 1, ']')
)
)
) AS after_remove
FROM
(SELECT '["a", "b", "c", "b"]') AS j (j)
;
Output:
before_remove |
after_remove |
["a", "b", "c", "b"] |
["a", "b", "c"] |
In this case, depending on the number of entries found, JSON_SEARCH
may return a JSON array of paths rather than a single path. Before applying JSON_UNQUOTE
, therefore, we need to extract the specific path from the array matching the required occurrence, for which we can use JSON_EXTRACT
. The CONCAT
expression returns the corresponding path for JSON_EXTRACT
(and then we get a path to use with JSON_REMOVE
– yes, I know, it is a little confusing).
Finally, in order to be able to remove all occurrences, you need to loop over the results of the path array returned by JSON_SEARCH
and either sequentially run JSON_REMOVE
with each path or somehow build and evaluate a single dynamic JSON_REMOVE
expression including all paths. Either way you need to do this in a custom-created function.
Alternatively you could try solving this in a set-based manner, for instance using the JSON_TABLE
function to turn the path array – output of JSON_SEARCH
– into a row set, one path per row, and then using a recursive CTE on the set, applying each path one by one and getting the last result as the final JSON.
The options are there and I am leaving the solution as an exercise for the reader. I will only add that both queries above are available online at db<>fiddle.