3

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.

0

3 Answers 3

4

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.

0

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]');

1
  • This is removal by index, not by value.
    – Marcus
    May 5 at 10:30
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.