By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Fruit ( id INT, fruit VARCHAR(25) )
INSERT INTO Fruit VALUES
(1,'Apple'),
(2,'Orange')
Records: 2 Duplicates: 0 Warnings: 0
CREATE TABLE FruitColor ( fruit_id INT, color VARCHAR(25) )
INSERT INTO FruitColor VALUES
(1,'Red'),
(1,'Green'),
(2,'Orange')
Records: 3 Duplicates: 0 Warnings: 0
SELECT f.*
FROM Fruit AS f
JOIN FruitColor AS fc
ON f.id = fc.fruit_id
| id | fruit |
|---|---|
| 1 | Apple |
| 1 | Apple |
| 2 | Orange |
SELECT JSON_PRETTY( 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
| Result |
|---|
| [ { "id": 1, "fruit": "Apple", "state": [ "Red", "Green" ] }, { "id": 2, "fruit": "Orange", "state": [ "Orange" ] } ] |