CODEWITHSUNDEEP
mysql
Claudio Veggiotti
Claudio Veggiotti

Reputation: 1

How to remove an element from MySQL's JSON array inside an object?

I have a mysql table "tbl1" with a JSON field "numbers" containing values like this:

{ "start" : ["101","102","104"], "final" : ["102","105","106"] }

How can i update "numbers" to remove a specific element (i.e. "102") from "start" and "final" ?

I know i have to use a combination of JSON_REMOVE and JSON_SEARCH but i don't find the solution.

Upvotes: 0

Views: 353

Answers (1)

Akina
Akina

Reputation: 42622

WITH RECURSIVE
cte AS ( SELECT JSON_REMOVE(json_field, JSON_UNQUOTE(JSON_SEARCH(json_field, 'one', '102'))) json_field
         FROM source_table
         UNION ALL
         SELECT JSON_REMOVE(json_field, JSON_UNQUOTE(JSON_SEARCH(json_field, 'one', '102'))) json_field
         FROM cte
         WHERE JSON_SEARCH(json_field, 'one', '102') )
SELECT json_field
FROM cte
WHERE JSON_SEARCH(json_field, 'one', '102') IS NULL;

and respectively

UPDATE source_table
JOIN (  WITH RECURSIVE
        cte AS ( SELECT id, JSON_REMOVE(json_field, JSON_UNQUOTE(JSON_SEARCH(json_field, 'one', '102'))) json_field
                 FROM source_table
                 UNION ALL
                 SELECT id, JSON_REMOVE(json_field, JSON_UNQUOTE(JSON_SEARCH(json_field, 'one', '102'))) json_field
                 FROM cte
                 WHERE JSON_SEARCH(json_field, 'one', '102') )
        SELECT id, json_field
        FROM cte
        WHERE JSON_SEARCH(json_field, 'one', '102') IS NULL
     ) processed USING (id)
SET source_table.json_field = processed.json_field;

Upvotes: 1

Related Questions