Is it possible that converting inner join select result to nested list JSON?

Question

I am developing an API controller with Spring.

I have two tables and they are one-to-many relationships.
One video can have multiple products.

I can select the video and product information with join query.
The select result is like below:

The JSON What I would like to return is below:

{
"videos": [{
    "video_id": "V0001",
    "video_nm": "Video001",
    "description": "Some text",
    "thumbnail": "path/img/aaa.jpg",
    "reg_dt": "11-30-2019",
    "products": [{
        "product_id": "P0001",
        "product_nm": "Product001",
        "description": "Some text",
        "info_url": "http://product.com"
    }, ...]
}, ...]
}

Is it possible to create the above JSON with the query result? Or should I change the JSON format?

Answer 1

To format the JSON in MySQL, you need to use the JSON_OBJECT() function and the JSON_ARRAYAGG() functions.

Something like the following — but I have not tested it.

SELECT JSON_OBJECT('videos', JSON_ARRAYAGG(vid_subquery.video)) AS videos
FROM (
  SELECT
    JSON_OBJECT(
      'video_id', v.video_id,
      'video_nm', v.video_nm,
      'description', v.description,
      'thumbnail', v.thumbnail,
      'reg_dt', v.reg_dt,
      'products', JSON_ARRAYAGG(
        JSON_OBJECT(
          'product_id', p.product_id,
          'product_nm', p.product_nm,
          'description', p.description,
          'info_url', p.info_url
        )
      )
    ) AS video
  FROM video AS v
  LEFT OUTER JOIN product AS p ON p.video_id = v.video_id
  GROUP BY v.video_id
) AS vid_subquery

If that seems too difficult, then you should follow the advice in the comments and write a Java mapper to do it.