Get SQL server record details as JSON for each record individually

Question

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

What is the expected output when a department has more than one employee? For example if another employee named John is added with department 11? — Andrea, May 11 '20 at 09:34
Should be in the Emp Array Like {"id": 11,"name": "IT","Emp":[{"Id": 2,"name": "scott"}, {"Id": 3,"name": "John"}]} — Rasmita Dash, May 11 '20 at 14:53

score 2 · Answer 1 · answered May 11 '20 at 14:59

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

Get SQL server record details as JSON for each record individually

1 Answers1