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
All you need to do is (trivially) alias your table properly and then use
AUTO
instead ofPATH
:Which outputs (after applying a format prettifier):