I have MySQL DB with a table that has a JSON column.
The sample data in the columns looks as below:
[{"id": 1, "value": 23.4}, {"id": 2, "value": 54.3}, {"id": 3, "value": 4.33}]
I wanted to update the value property of all the objects in the column to 0.0(let's say)
My attempt:
update table_name set json_col = JSON_SET(json_col, '$[*].value', 0.0);
The error statement from MySQL:
ERROR 3149 (42000): In this situation, path expressions may not contain the * and ** tokens.
How can I make this work?
Add a comment
|
1 Answer
You cannot perform multiple update. You must either update one-by-one value in recursive CTE or parse/replace/reconstruct:
UPDATE test
JOIN ( SELECT id, JSON_ARRAYAGG(JSON_REPLACE(object, '$.value', 0)) value
FROM test
CROSS JOIN JSON_TABLE( test.value,
'$[*]' COLUMNS (object JSON PATH '$')) jsontable
GROUP BY id ) data_for_update USING (id)
SET test.value = data_for_update.value;
fiddle (with step-by-step explanations).
1 Comment
vamsi
This perfectly solves my problem, thanks a lot.