Get SQL server record details as JSON for each record individually

user_3867678 0
Viewed 31

I have 2 tables. Department & Employee as below : 

    Department
    |----------------------|
    |   Id    |   Name     |
    |---------|------------|
    |   10    |    Admin   |
    |---------|------------|
    |   11    |    IT      |
    |---------|------------|

    Employee
    |----------------------|--------------|
    |   Id    |   Name     | DepartmentId |
    |---------|------------|--------------|
    |   1     |    Peter   |    10        |
    |---------|------------|--------------|
    |   2     |    Scott   |    11        |
     --------------------------------------

I need the JSON details of each department individually as below:

|----------------------|------------------------------------------------------------ |
|   DepartmentId       |   JSONDeatails                                              |
|--------------------- |------------------------------------------------------------ |
|   10                 |{"id": 10,"name": "Admin","Emp": [{"Id": 1,"name": "peter"}]}|
|----------------------|-------------------------------------------------------------|
|   11                 |{"id": 11,"name": "IT","Emp":[{"Id": 2,"name": "scott"}]}    |
|----------------------|-------------------------------------------------------------|

I tried below query which is returning the entire table data as one JSON.

 SELECT     D.Id, D.name, Emp.Id, Emp.Name FROM Department D
 LEFT JOIN Employee Emp ON D.Id= Emp.DepartmentId FOR JSON AUTO

I am getting the response as below:

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{"id": 10,"name": "Admin","Emp": [{"Id": 1,"name": "peter"}]},{"id": 11,"name": "IT","Emp":[{"Id": 2,"name": "scott"}]}]
sql-server-json sql-server-2016
1 Answers

After many trials, I could figure out the query that gives JSON for each indiviadual record. Here it is:

SELECT D.Id, 
     (SELECT Di.Id, Di.name ,Emp.Id, Emp.Name FROM Department Di 
        LEFT JOIN Employee Emp ON Di.Id= Emp.DepartmentId 
        WHERE Di.Id = D.id FOR JSON AUTO) AS Details 
FROM Department D
user_38676780
Related