Post reply

convert Query output into JSON Format.

  • October 26, 2018 at 6:53 am

    #416341

    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 the output in below format i am using sql server 2016.Application id should not be duplicate for(example ApplicationID:1)

    Required output Format:
    [{"ApplicationID":1,"Roles":[1,5]},{"ApplicationID":3,"Roles":[5]}]

  • October 26, 2018 at 7:01 am

    #2011325

    jkramprakash - Friday, October 26, 2018 6:53 AM

    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 the output in below format i am using sql server 2016.Application id should not be duplicate for(example ApplicationID:1)

    Required output Format:
    [{"ApplicationID":1,"Roles":[1,5]},{"ApplicationID":3,"Roles":[5]}]

    Can you post the DDL (create table) script, some sample data as an insert statement and the desired results for the sample data please?
    😎

  • October 26, 2018 at 7:07 am

    #2011327

    CREATE TABLE USERROLES(ApplicationID int,Roleid INT)

    Insert into userroles values(1,1);
    insert into userroles values(1,5)
    insert into userroles values(3,5)

  • October 26, 2018 at 7:55 am

    #2011344

    You will need to concatenation the Roles values, this will do the job.
    😎


    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- https://www.sqlservercentral.com/Forums/2006590/convert-Query-output-into-JSON-Format

    DECLARE @USERROLES TABLE (ApplicationID int,Roleid INT);

    Insert into @USERROLES
    values(1,1),(1,5),(3,5);

    SELECT
     UR.ApplicationID
    ,STRING_AGG(UR.RoleId,',') AS 'Roles'
    FROM  @USERROLES UR
    GROUP BY UR.ApplicationID
    FOR JSON PATH;

    Output

    [{"ApplicationID":1,"Roles":"1,5"},{"ApplicationID":3,"Roles":"5"}]

  • October 26, 2018 at 11:47 pm

    #2011441

    STRING_AGG function is not working in my SQL SERVER version(Microsoft SQL Server 2016 (SP1) (KB3182545)).Any other method to get the output?

  • October 27, 2018 at 12:53 am

    #2011443

    I used below query to get the output.it is coming but i want to replace ROLEID in the query output.

    select UR1.ApplicationID ,(select UR2.RoleID from UserRoles UR2 where UR2.UserID = 11 and UR2.ApplicationID = UR1.ApplicationID for json path) Roles from UserRoles UR1 where UR1.userid = 11 and UR1.ApplicationID is not null group by UR1.ApplicationID for json path

    Query output 

    --[{"ApplicationID":1,"Roles":[{"RoleID":1},{"RoleID":5}]},{"ApplicationID":3,"Roles":[{"RoleID":5}]}]

    Required output

    [{"ApplicationID":1,"Roles":[1,5]},{"ApplicationID":3,"Roles":[5]}]

  • October 27, 2018 at 12:58 am

    #2011444

    jkramprakash - Friday, October 26, 2018 11:47 PM

    STRING_AGG function is not working in my SQL SERVER version(Microsoft SQL Server 2016 (SP1) (KB3182545)).Any other method to get the output?

    Then you'll have to use another string concatenation method😉
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- https://www.sqlservercentral.com/Forums/2006590/convert-Query-output-into-JSON-Format

    DECLARE @USERROLES TABLE (ApplicationID int,Roleid INT);

    Insert into @USERROLES
    values(1,1),(1,5),(3,5);

    -- SQL 2017+
    SELECT
     UR.ApplicationID
    ,STRING_AGG(UR.RoleId,',') AS 'Roles'
    FROM  @USERROLES UR
    GROUP BY UR.ApplicationID
    FOR JSON PATH;

    -- SQL 2016
    SELECT DISTINCT
      ApplicationID AS 'ApplicationID'
     ,STUFF((SELECT CONCAT(',',Roleid)
      FROM @USERROLES SUR
      WHERE SUR.ApplicationID = UR.ApplicationID
      FOR XML PATH('')),1,1,N'')   AS 'Roles'
    FROM  @USERROLES UR
    FOR JSON PATH, ROOT('');

  • October 27, 2018 at 3:32 am

    #2011446

    Thank you very much.

  • October 27, 2018 at 3:36 am

    #2011447

    jkramprakash - Saturday, October 27, 2018 3:32 AM

    Thank you very much.

    You are very welcome.
    😎

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply