Is it possible to remove a JSON array element by value in MySQL?

Question

In MySQL, it's possible to remove an array element by index:

SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`;
+------------+
| result |
+------------+
| ["a", "c"] |
+------------+

Is it possible to remove by value instead - in this case the element(s) matching the string "b"?

Based on the documentation, it's possible to find the index via JSON_SEARCH and then passing it to JSON_REMOVE, however, I was wondering if there's an expression/function to do it in a single function.

Answer 1

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.

Answer 2

Just use json_remove as below:

SET @j = '["a", ["b", "c", "d"], "e"]';
SELECT JSON_REMOVE(@j, '$[1][2]');

or

SET @j = '{"e1": "a", "e2": ["b", "c", "d"], "e3": "e"}';
select JSON_REMOVE(@j, '$."e2"[1]');

• Marcus: This is removal by index, not by value.

Answer 3

I had a syntax error with the above query, but it really helped me sort it out. This worked for me.

UPDATE role AS r 
SET permissions = JSON_REMOVE(
 r.permissions,
 JSON_UNQUOTE(JSON_SEARCH(r.permissions, 'one', 'my_key'))
) 
WHERE r.custom = true