Return column of joined rows as array instead of object-CodePudding

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.

CodePudding user response：

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