convert query output into json format in sql server

Question

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

Answer 1

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.