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

score 0 · Answer 1 · answered Mar 11 '21 at 19:00

Here's a solution I tested:

mysql> set @j='[{"a":1},{"a":2}]';

mysql> with recursive cte as (
    select 0 as i, json_set(@j, '$[0].b', 'new val') as j 
    union all 
    select cte.i+1, json_set(cte.j, concat('$[',cte.i+1,'].b'), 'new val') 
    from cte 
    where cte.i<json_length(@j)-1
  ) select * from cte
    where i = json_length(@j)-1;
+------+------------------------------------------------------+
| i    | j                                                    |
+------+------------------------------------------------------+
|    1 | [{"a": 1, "b": "new val"}, {"a": 2, "b": "new val"}] |
+------+------------------------------------------------------+

I have to say this begs the question whether JSON is worth it. It would be far easier if you used normal rows and columns instead of JSON.

score 0 · Accepted Answer · answered Mar 11 '21 at 19:12

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

modify every object in json array in mysql

2 Answers2