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

CodePudding user response：

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

CodePudding user response：

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).