Return column of joined rows as array instead of object

May 29, 2022

I’m having a problem which is a little difficult for me to explain.

I have two database tables:

| ID | FRUIT |
| 1  | Apple |
| 2  | Orange|

| FRUIT_ID | COLOR |
| 1       | Red   |
| 1       | Green |
| 2       | Orange|

The idea behind this: A Fruit can have multiple colors.
To get all the colors from all the fruits I have to join them:

SELECT a.*, b.color FROM Fruit a, FruitColor b WHERE a.id = b.fruit_id

| ID | FRUIT | COLOR |
| 1  | Apple | Red   |
| 1  | Apple | Green |
| 2  | Orange| Orange|

The Json would look like this:

[{
    ID: "1",
    FRUIT: "Apple",
    COLOR: "Red"
},
{
    ID: "1",
    FRUIT: "Apple",
    COLOR: "GREEN"
},
{
    ID: "2",
    FRUIT: "Orange",
    COLOR: "Orange"
}]

And here comes the problem
I actually don’t want to return an object for every extra color, I need them in an array like this:

[{
    ID: "1",
    FRUIT: "Apple",
    COLOR: ["Red", "Green"]
},
{
    ID: "2",
    FRUIT: "Orange",
    COLOR: "Orange"
}]

How can I achieve this?
It doesn’t matter if I have to change the DB structure or the query, any solution to this would help.

>Solution :

You can use JSON_ARRAYAGG function twice in order to encapsulate the colors firstly, and objects with the second apply such as

SELECT JSON_ARRAYAGG(js) AS Result
  FROM (SELECT JSON_OBJECT('id',
                           f.id,
                           'fruit',
                           f.fruit,
                           'state',
                           JSON_ARRAYAGG(fc.color)) AS js
          FROM Fruit AS f
          JOIN FruitColor AS fc
            ON f.id = fc.fruit_id
         GROUP BY f.id, f.fruit) j

Demo