CODEWITHSUNDEEP
mysqlsqlarraysjsonaggregate-functions
Thisara Dilshan
Thisara Dilshan

Reputation: 17

MySQL append data to each JSON Object within JSON Array

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"}]

Upvotes: 0

Views: 82

Answers (1)

GMB
GMB

Reputation: 222482

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

Upvotes: 1

Related Questions