How to present row data in JSON format in SQL Server 2014

user_12134418 0
Viewed 55

I want to present the data which is getting after joined with another table. It is getting multiple records for one particular Id. So I want to display those multiple records in JSON format.

I have the below data.

Declare @Employees Table (EmpId int, EmpName Varchar(100))
INSERT INTO @Employees
VALUES(1,'RAM')
,(2,'RAJ')
,(3,'LAXMAN')

    Declare @Subjects Table (EmpId int, Subject Varchar(100))
    INSERT INTO @Subjects
    VALUES(1,'Maths')
    ,(1,'Science')
    ,(1,'Physics')
    ,(2,'Physics')
    ,(3,'Maths')
    ,(3,'Physics')

I have tried with this query.

SELECT E.EmpId
    ,Subject
FROM @Employees E
LEFT JOIN (
    SELECT EmpId
        ,'{' + STUFF((
                SELECT ',"' + NULLIF(Subject, '') + '"'
                FROM @Subjects AS CG1
                WHERE CG1.EmpId = CG2.EmpId
                FOR XML PATH('')
                ), 1, 1, '') + '}' AS Subject
    FROM @Subjects AS CG2
    GROUP BY EmpId
    ) SUB ON SUB.EmpId = E.EmpId

But I want the result like this.

    EmpId   Subject
-----------------------------------------------------
    1       {"1":"Maths","2":"Science","3":"Physics"}
    2       {"1":"Physics"}
    3       {"1":"Maths","2":"Physics"}

Please let me know if you want any further information is needed. I would appreciate your help.

Thanks

tsql sql-server
1 Answers

You were very close, you need to add a ROW_NUMBER to the subquery too. I've also switched to CONCAT for easy implicit conversions:

SELECT E.EmpId,
       Subject
FROM @Employees E
     LEFT JOIN (SELECT EmpId,
                       '{' + STUFF((SELECT CONCAT(',"', ROW_NUMBER() OVER (ORDER BY CG1.Subject), '":"', NULLIF(Subject, ''), '"')
                                    FROM @Subjects CG1
                                    WHERE CG1.EmpId = CG2.EmpId
                                    ORDER BY Subject
                                    FOR XML PATH('')),1,1,'') + '}' AS Subject
                FROM @Subjects CG2
                GROUP BY EmpId) SUB ON SUB.EmpId = E.EmpId;

Note that the order of the subjects in your table isn't preserved, due to that there is no way to preserve it with an ORDER BY (at least not with the data we have). Therefore 'Physics' has a number of 2 instead of 3 for Empid 1

user_20299830
Related