MySQL append data to each JSON Object within JSON Array

Question

in this table - mytable i have a json column - col and its content
[{"id": 1, "data1": "abc", "data2": "xyz"}, {"id": 2, "data1": "def", "data2": "ghi"}]

there is an another table - product

+----+---------+
| id | name    |
+----+---------+
|  1 | pro 1   |
+----+---------+
|  2 | pro 2   |
+----+---------+

is there a way that I can append name to each JSON Object in the JSON Array Ex- [{"id": 1,"name":"pro 1", "data1": "abc", "data2": "xyz"}, {"id": 2,"name":"pro 2", "data1": "def", "data2": "ghi"}]

Split the array to separate objects, join with additional data table, update objects, join the array back. — Akina
– Akina, Commented Nov 23, 2020 at 8:01
What does select version(); show? And do you want to modify what's in mytable.col or just select with name added? — ysth
– ysth, Commented Nov 23, 2020 at 8:05
The version is too old. The query will be too complex. I'd recommend to upgrade the server. If not then create iterational stored procedure. — Akina
– Akina, Commented Nov 23, 2020 at 8:11

GMB · Accepted Answer · 2020-11-23 09:34:35Z

1

In MySQL 8.0, you can use json_table() for this. The idea is to unnest the json array to rows, join the product table and then re-aggregate.

select json_arrayagg(
    json_object(
        'id',    x.id,
        'data1', x.data1,
        'data2', x.data2,
        'name',  p.name
    )
) as col
from mytable t
cross join json_table(
    t.col, 
    '$[*]' columns ('id' int, 'data1' varchar(50), 'data2' varchar(50))
) x
inner join product p on p.id = x.id
group by x.id

answered Nov 23, 2020 at 9:34

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL append data to each JSON Object within JSON Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related