Generate nested JSON for data

Question

I have the following table and sample data:

CatID        int
Catname      nvarchar(255)
product_ID   int
product_name nvarchar(255)

catid Catname      product_ID product_name
----- ------------ ---------- ------------
100   mycatname100 1          productname1
100   mycatname100 2          productname2
100   mycatname100 3          productname3
200   mycatname200 1          productname1
200   mycatname200 2          productname2
200   mycatname200 3          productname3
300   mycatname300 1          productname1
300   mycatname300 2          productname2
300   mycatname300 3          productname3

How can i get a JSON format like below:

[
  {"catid":"100","catname":"mycatname100","products":[{"product_id":"1","product_name":"productName1"},{"product_id":"2","product_name":"productName2"},{"product_id":"3","product_name":"productName3"}]},
  {"catid":"200","catname":"mycatname200","products":[{"product_id":"1","product_name":"productName1"},{"product_id":"2","product_name":"productName2"},{"product_id":"3","product_name":"productName3"}]},
  {"catid":"300","catname":"mycatname300","products":[{"product_id":"1","product_name":"productName1"},{"product_id":"2","product_name":"productName2"},{"product_id":"3","product_name":"productName3"}]}
]

Answer 1

The database design could use some normalization. Having said that, you can use a nested query to create nested JSON:

SELECT catid, catname, products = (
    SELECT product_id, product_name
    FROM t AS x
    WHERE x.catid = t.catid
    FOR JSON PATH
)
FROM t
GROUP BY catid, catname
FOR JSON AUTO

Demo on db<>fiddle