SQL - Json query

Question

I have tables similar to below where it has one to many mapping

DECLARE @T1 TABLE(RootId int, [Name] varchar(50))
DECLARE @T2 TABLE(Id int,RootId int, Category varchar(50))

INSERT INTO @T1
VALUES(1,'Some Name 12121'),(2,'Some Name 343434')

INSERT INTO @T2
VALUES(100,1,'Category 3333'),
        (101,1,'Category 2222'),
        (102,1,'Category 4444'),
        (103,1,'Category 5555'),
        (104,2,'Category 1111'),
        (105,2,'Category 77777')

I am expecting to write query so it produce json as below format, where it had item and array of sub

    [
  {
    "item": {
      "rootId": 1,
      "name": "Some Name 12121",
      "sub": [
        {
          "id": 100,
          "category": "Category 3333"
        },
        {
          "id": 101,
          "category": "Category 2222"
        }
      ]
    }
  }
]

I have tried below but could not achieve the expected output

SELECT T1.RootId,T1.Name,T2.Id AS [Sub.Id],T2.Category as [Sub.Category]
FROM @T1 T1
    INNER JOIN @T2 T2 ON T1.RootId = T2.RootId
FOR JSON PATH, root('item')

Is there a way to query so that it produce expected Json

Answer 1

All you need to do is (trivially) alias your table properly and then use AUTO instead of PATH:

SELECT T1.RootId,T1.Name,sub.Id AS Id,sub.Category as Category
FROM @T1 T1
    INNER JOIN @T2 sub ON T1.RootId = sub.RootId
FOR JSON AUTO, ROOT('item');

Which outputs (after applying a format prettifier):

{
    "item": [
        {
            "RootId": 1,
            "Name": "Some Name 12121",
            "sub": [
                {
                    "Id": 100,
                    "Category": "Category 3333"
                },
                {
                    "Id": 101,
                    "Category": "Category 2222"
                },
                {
                    "Id": 102,
                    "Category": "Category 4444"
                },
                {
                    "Id": 103,
                    "Category": "Category 5555"
                }
            ]
        },
        {
            "RootId": 2,
            "Name": "Some Name 343434",
            "sub": [
                {
                    "Id": 104,
                    "Category": "Category 1111"
                },
                {
                    "Id": 105,
                    "Category": "Category 77777"
                }
            ]
        }
    ]
}