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 to split the result for JSON_SEARCH(...'all'...) into separate path elements to pass to JSON_REMOVE — Nick
– Nick, Commented Dec 1, 2019 at 20:49
are you using a stored procedure? You could do this with a loop — Geoduck
– Geoduck, Commented Dec 5, 2019 at 23:28

Paul Spiegel · Accepted Answer · 2019-12-06 11:02:27Z

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.

Collectives™ on Stack Overflow

Remove all JSON objects from JSON array in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related