MySQL: update specific property value of a JSON object in a JSON Array-CodePudding

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?

CodePudding user response：

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