Remove all JSON objects from JSON array in MySQL

Question

I have this so far, and it removes only one object, and there are 4 that should be removed.

SET @JSON = '[{"one": 5},{"two":2},{"one": 5},{"three":3},{"one": 5},{"one": 5}]';
SET @TO_REMOVE = '{"one": 5}';

SET @JSON = REPLACE(@JSON, @TO_REMOVE, '"DELETED"');
SET @JSON = JSON_REMOVE(@JSON, JSON_UNQUOTE(JSON_SEARCH(@JSON, 'one', 'DELETED')));

SELECT @JSON;

#returns the following:
[{"two": 2}, "DELETED", {"three": 3}, "DELETED", "DELETED"]

What I really want is a method like the above, but instead of putting in 'one' in JSON_SEARCH I want to put 'all'. Does anyone know how to do this?

SELECT JSON_UNQUOTE(JSON_SEARCH(@JSON, 'one', 'DELETED')) AS `Single`; #returns: $[0]
SELECT JSON_UNQUOTE(JSON_SEARCH(@JSON, 'all', 'DELETED')) AS `Multi`;  #returns: ["$[0]", "$[2]", "$[4]", "$[5]"]

Is there a way to pass in each value ["$[0]", "$[2]", "$[4]", "$[5]"] into JSON_REMOVE, like you have ... in JavaScript for function arguements.

Thanks, please let me know alternative methods as well.

Unless you upgrade to MySQL 8, you're probably stuck with trying to use a technique as in [this question](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) to split the result for `JSON_SEARCH(...'all'...)` into separate path elements to pass to `JSON_REMOVE` — Nick, Dec 01 '19 at 20:49
are you using a stored procedure? You could do this with a loop — Garr Godfrey, Dec 05 '19 at 23:28

score 1 · Accepted Answer · answered Dec 06 '19 at 11:02

The "brutal" way: Just REPLACE them:

SET @JSON = '[{"one": 5},{"two":2},{"one": 5},{"three":3},{"one": 5},{"one": 5}]';
SET @TO_REMOVE = '{"one": 5}';

SET @JSON = REPLACE(@JSON, CONCAT(@TO_REMOVE, ','), '');
SET @JSON = REPLACE(@JSON, CONCAT(',', @TO_REMOVE), '');
SET @JSON = REPLACE(@JSON, @TO_REMOVE, '');

SELECT @JSON;

Result:

| @JSON                   |
| ----------------------- |
| [{"two":2},{"three":3}] |

View on DB Fiddle

Note that we have to handle three cases:

A matching item followed by another item (has , behind it)
A matching item which ist the last item (not followed by any item but has a , in front of it)
A matching item which is the only item in the array (not surrounden by ,)

In MySQL 8: Unpack the array with JSON_TABLE, then filter and pack the elements again with JSON_ARRAYAGG:

SET @JSON = '[{"one": 5},{"two":2},{"one": 5},{"three":3},{"one": 5},{"one": 5}]';
SET @TO_REMOVE = '{"one": 5}';

SET @JSON = (
  SELECT JSON_ARRAYAGG(item)
  FROM JSON_TABLE(
    @JSON,
    '$[*]' columns (item JSON path '$')
  ) t
  WHERE item <> CAST(@TO_REMOVE AS JSON)
);

SELECT @JSON;

Result:

| @JSON                      |
| -------------------------- |
| [{"two": 2}, {"three": 3}] |

View on DB Fiddle

Note that the order of the elements can change, because we cannot control the order for JSON_ARRAYAGG.

Remove all JSON objects from JSON array in MySQL

1 Answers1