[SOLVED] How to inner join and parse data as JSON using json1

How to inner join and parse data as JSON using json1

For example, let's say I've a table called Movie with 2 columns - [id,title]

Data:

1, killbill
2, endgame

and another table as Cast with 2 columns - [id,name]

Data:

1, Uma
1, David
2, Robert

Cast table has actors and id is same as movie.

The output I want from SELECT query is something like:

1, killbill, [uma,David]
2, endgame,[Robert]

What I tried:

select m.*,json_array(c.name) from movie m inner join cast c on c.id = m.id

But it returns:

1   killbill    [David]
1   killbill    [Uma]
2   endgame     [Robert]

Please suggest the right way to group data. I also tried group by, but then it returns less data.

If I use json_group_array, I get only one movie with all cast

1   killbill    ["David","Uma","Robert"]

Solution

You must group by movie and use the aggregate function json_group_array() instead of json_array():

select m.id, m.title, json_group_array(c.name) names
from movie m inner join cast c 
on c.id = m.id 
group by m.id, m.title

See the demo.
Results:

| id  | title    | names           |
| --- | -------- | --------------- |
| 1   | killbill | ["David","Uma"] |
| 2   | endgame  | ["Robert"]      |