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