How to remove multiple values of different dictionaries stored in arrays with JSON_REMOVE?

Question

I have the following json doc in MYSQL JSON field called test:

[
  {"a": "1", "b": "2"},
  {"a": "1", "b": "-2"},
  {"a": "2", "b": "3"},
  {"a": "2", "b": "-3"},
  {"a": "3", "b": "4"}
]

CREATE TABLE `test` (`test` JSON);

INSERT INTO `test` VALUES
    (JSON_ARRAY(JSON_OBJECT('a', '1', 'b', '2'),
                JSON_OBJECT('a', '1', 'b', '-2'),
                JSON_OBJECT('a', '2', 'b', '3'),
                JSON_OBJECT('a', '2', 'b', '-3'),
                JSON_OBJECT('a', '3', 'b', '4'))),
    (JSON_ARRAY()),
    (JSON_ARRAY());

SELECT JSON_UNQUOTE(JSON_SEARCH(`test`, 'all', 1, null, '$[*].a')) `data`
  FROM `test`;

+----------------------+
| data                 |
+----------------------+
| ["$[0].a", "$[1].a"] |
| NULL                 |
| NULL                 |
+----------------------+

And I want to remove all dictionaries that have key/value "a": "1". So I tried this:

UPDATE `test`
   SET `test` = JSON_REMOVE(`test`, JSON_UNQUOTE(JSON_SEARCH(`test`,
                                                             'all', 
                                                             1, 
                                                             null, 
                                                             '$[*].a')));

The expected result that I wanted is, but of course it doesn't work:

// This is an expected result after update
SELECT JSON_UNQUOTE(JSON_SEARCH(`test`, 'all', 1, null, '$[*].a')) `data`, `test`   FROM `test`;


+----------------------+------------------------------------------------------------------------------------------------------------------+                                                                                                               
| data                 | test                                                                                                             |                                                                                                               
+----------------------+------------------------------------------------------------------------------------------------------------------+                                                                                                               
| NULL                 | [{"a": "2", "b": "3"}, {"a": "2", "b": "-3"}, {"a": "3", "b": "4"}] |                                                                                                               
| NULL                 | []                                                                                                               |                                                                                                               
| NULL                 | []                                                                                                               |                                                                                                               
+----------------------+------------------------------------------------------------------------------------------------------------------+

Note that the mysql version is 8.0

Thank you

Barbaros Özhan · Accepted Answer · 2022-03-09 17:43:16Z

1

You can use Recursive CTE along with JSON_EXTRACT() function in order to dynamically generate the indexes, of which don't match a = 1, of objects within the array such as

WITH RECURSIVE cte AS
(
 SELECT 0 i
  UNION ALL
 SELECT i + 1 i
   FROM cte
  WHERE i + 1 < ( SELECT JSON_LENGTH(jsdata) FROM `test` )  
)
SELECT JSON_ARRAYAGG(JSON_EXTRACT(jsdata, CONCAT('$[',i,']'))) AS Result
  FROM `test`,
       cte
 WHERE JSON_EXTRACT(jsdata, CONCAT('$[',i,'].a')) != "1"

provided that the version of the DB is 8+

Demo

answered Mar 9, 2022 at 17:43

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CuriousPanda Over a year ago

Thank you for your reply, but doesn't exist nothing less tricky? Btw your solution seems to be really cool and stylish. P.s. Yes the MySQL version is 8+

CuriousPanda Over a year ago

I added more information after suggestion of @Akina to make the example more understandable

Akina · Accepted Answer · 2022-03-09 19:09:19Z

1

WITH 
-- enumerate rows - unique value per row is needed for reconstruction
cte1 AS (
    SELECT *, ROW_NUMBER() OVER () rn
    FROM test 
    ),
-- decompose JSON array to separate objects properties, 
-- select only those which does not contain unneeded value
cte2 AS (
    SELECT cte1.*, jsontable.a, jsontable.b
    FROM cte1
    CROSS JOIN JSON_TABLE(cte1.test, 
                          '$[*]' COLUMNS ( a VARCHAR(255) PATH '$.a',
                                           b VARCHAR(255) PATH '$.b')) jsontable
    WHERE jsontable.a <> 1
    ),
-- reconstruct the array w/o unneeded objects back
cte3 AS (    
    SELECT test, rn, JSON_ARRAYAGG(JSON_OBJECT('a', a, 'b', b)) new_test
    FROM cte2
    GROUP BY test, rn
    )
-- update sourdce table
UPDATE test
JOIN cte3 ON test.test = cte3.test
SET test.test = cte3.new_test;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=05e4db09be79152c37b9b482b8bff170

If the table in practice contains primary key or at least unique index then you can simplify the query (cte1 not needed, final join can be performed by this PK/unique).

answered Mar 9, 2022 at 19:09

Akina

43.2k6 gold badges17 silver badges30 bronze badges

1 Comment

CuriousPanda Over a year ago

Thank for the code and a complete explanation of it, I really appreciate it.

Collectives™ on Stack Overflow

How to remove multiple values of different dictionaries stored in arrays with JSON_REMOVE?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related