sqlsql-serversql-server-2016sql-server-json

SQL - Json query


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


Solution

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