MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.20


DROP TABLE IF EXISTS `tbl`;

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` TINYINT,
  `ProductID` CHAR(3),
  `color` VARCHAR(10),
  `size` CHAR(1)
);

INSERT INTO `tbl`
  (`id`, `ProductID`, `color`, `size`)
VALUES
  (1, 'abc', 'red', 'L'),
  (2, 'abc', 'green', 'M'),
  (3, 'abc', 'yellow', 'S'),
  (4, 'def', 'purple', 'L'),
  (5, 'def', 'brown', 'M'),
  (6, 'def', 'pink', 'S');
SELECT `id`, `ProductID`, `color`, `size`
FROM `tbl`;


id
ProductID
color
size


1
abc
red
L

2
abc
green
M

3
abc
yellow
S

4
def
purple
L

5
def
brown
M

6
def
pink
S


SELECT JSON_PRETTY(
  CONCAT(
    '{"status": true, ',
    '"message": "All Product Logs has been fetched Successfully", ',
    '"products": [',
    (
      SELECT
        GROUP_CONCAT(`der`.`json`)
      FROM (
        SELECT
          JSON_OBJECT(
            'ProductID', `ProductID`,
            'colors', JSON_ARRAYAGG(`color`),
            'sizes', JSON_ARRAYAGG(`size`)
          ) `json`
        FROM
          `tbl`
        GROUP BY
          `ProductID`
      ) `der`
    ),
    ']}'
  )
) `json_response`;


json_response


{
  "status": true,
  "message": "All Product Logs has been fetched Successfully",
  "products": [
    {
      "sizes": [
        "L",
        "M",
        "S"
      ],
      "colors": [
        "red",
        "green",
        "yellow"
      ],
      "ProductID": "abc"
    },
    {
      "sizes": [
        "L",
        "M",
        "S"
      ],
      "colors": [
        "purple",
        "brown",
        "pink"
      ],
      "ProductID": "def"
    }
  ]
}