modify every object in json array in mysql

Question

Json Array - [{"a":1},{"a":2}]

Is there a way that I can update/set each object in this array with a single query ?.

I can update an specific object using `

SELECT JSON_SET('[{"a":1},{"a":2}]','$[0].b','new val') 

which results

[{"a": 1, "b": "new val"}, {"a": 2}] 

But I need

[{"a": 1, "b": "new val"}, {"a": 2, "b": "new val"}]

. I use mysql 8.0.22

Answer 1

If you have multiple "a" then make first a JSON table and then combine it back.

With miore complicate and nested json y ou must do more work on the JSON_TABLE

SELECT JSON_SET('[{"a":1},{"a":2}]','$[0].b','new val');


 

| JSON_SET('[{"a":1},{"a":2}]','$[0].b','new val') |
| :----------------------------------------------- |
| [{"a": 1, "b": "new val"}, {"a": 2}]             |

SELECT 
 JSON_ARRAYAGG(JSON_OBJECT("a", a, "b", "new valuze")) 
     FROM
       JSON_TABLE(
         '[{"a":1},{"a":2}]',
         "$[*]" COLUMNS(
           a VARCHAR(100) PATH "$.a"
         )
       ) AS  jt1;

| JSON_ARRAYAGG(JSON_OBJECT("a", a, "b", "new valuze"))          |
| :------------------------------------------------------------- |
| [{"a": "1", "b": "new valuze"}, {"a": "2", "b": "new valuze"}] |

db<>fiddle here