CODEWITHSUNDEEP
jsonsql-server
Ram
Ram

Reputation: 805

convert query output into json format in sql server

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

Upvotes: 3

Views: 5527

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 2

Daniel Corzo
Daniel Corzo

Reputation: 1075

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

Upvotes: 0

ParanoiK
ParanoiK

Reputation: 41

As I remember:

select ApplicationID ApplicationID ,Roleid Roles from UserRoles where userid=11 and applicationid is not null 
FOR JSON PATH, ROOT("UserRoles")

Upvotes: 0

Related Questions