MySQL 8.0

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