I want to display the below query output into JSON Format(Required output format)
select ApplicationID ApplicationID ,Roleid Roles from UserRoles where userid=11 and applicationid is not null
Output
ApplicationID Roles
1 1
1 5
3 5
i want to display below format i am using sql server 2016
Required output Format:
[{"ApplicationID":1,"Roles":[1,5]},{"ApplicationID":3,"Roles":[5]}]
The closest you get with pure T-SQL-JSON-support will be this:
DECLARE @tbl TABLE(ApplicationID INT, Roles INT);
INSERT INTO @tbl VALUES
(1,1)
,(1,5)
,(3,5);
SELECT t.ApplicationID
,Roles.Roles AS Roles
FROM @tbl t
INNER JOIN @tbl Roles ON Roles.ApplicationID=t.ApplicationID
GROUP BY t.ApplicationID,Roles.Roles
FOR JSON AUTO;
The result
[{"ApplicationID":1
,"Roles":[{"Roles":1}
,{"Roles":5}]
}
,{"ApplicationID":3
,"Roles":[{"Roles":5}]}
]
In AUTO mode the engine will "see" the join and pack this into an array of objects.
Regretfully FOR JSON does not support naked arrays ([1,2,3]). You will always get arrays of objects like [{Prop:Val},{Prop:Val},{Prop:Val}]...
But you can out-trick this with a correlated sub-query and some string aggregation (We need JSON_QUERY() to avoid quotes around the array):
SELECT t.ApplicationID
,JSON_QUERY('[' + STUFF((
SELECT CONCAT(',',Roles)
FROM @tbl t2
WHERE t2.ApplicationID=t.ApplicationID
FOR XML PATH('')
),1,1,'') + ']') AS Roles
FROM @tbl t
GROUP BY t.ApplicationID
FOR JSON PATH;
The result
[{"ApplicationID":1,"Roles":[1,5]}
,{"ApplicationID":3,"Roles":[5]}]
Can you use STRING_AGG() (v2017+)? In this case you can simplify the sub-select.
As I remember:
select ApplicationID ApplicationID ,Roleid Roles from UserRoles where userid=11 and applicationid is not null
FOR JSON PATH, ROOT("UserRoles")
If you are using SQL Server 2016+, you can use FOR JSON statement
SELECT
ApplicationID ApplicationID,
Roleid Roles
FROM UserRoles
WHERE userid = 11 AND applicationid IS NOT NULL
FOR JSON AUTO
Take a look into this: Format Query Results as JSON with FOR JSON
