How to aggregate JSON in SQL Server?

Question

I looked into SQL Server documentation, but didn't find something like JSON aggregate. This is structure of my table.

| Column Name         | Type               | Nullable | Properties | Description |
| ------------------- | ------------------ | -------- | ---------- | ----------- |
| employee_id         | INT(4)             | NO       |            |             |
| first_name          | NVARCHAR(100)      | NO       |            |             |
| last_name           | NVARCHAR(100)      | NO       |            |             |
| department_id       | INT(4)             | NO       |            |             |
| date                | DATETIMEOFFSET(10) | NO       |            |             |

What I am expecting is:

{
    department_id: 1,
    count: 2,
    employees: [
    {
        first_name: 'John',
        last_name: 'Doe'
        employee_id: 1
    },
    {
        first_name: 'Foo',
        last_name: 'Bar',
        employee_id: 2
    }]
}

From this query I was able to get this result.

SELECT
    ves.department_id,
    COUNT(1) AS count,
    (SELECT
         a.first_name,
         a.last_name,
         a.employee_id
     FROM 
         employee_departments a
     WHERE 
         a.department_id = ves.department_id 
     FOR JSON AUTO) AS employees
FROM 
    employee_departments ves
GROUP BY 
    ves.department_id;

But I am wondering is there any better approach to aggregate JSON? just like STRING_AGG()?

SELECT
    department_id,
    COUNT(1) as count,
    STRING_AGG(employee_id, ',')
FROM 
    employees_shifts 
GROUP BY 
    department_id;

Answer 1

You asked me in a comment to elaborate more... Well, your own code looks pretty close to what you seem to need. Try this:

a mockup table

DECLARE @tbl TABLE(department_id INT,employee_id INT,first_name VARCHAR(100),last_name VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,1,'John','Doe')
,(1,2,'Foo','Bar')
,(2,3,'some','more');

--the query

SELECT
    ves.department_id,
    COUNT(1) AS count,
    (SELECT
         a.first_name,
         a.last_name,
         a.employee_id
     FROM 
         @tbl a
     WHERE 
         a.department_id = ves.department_id 
     FOR JSON PATH) AS employees
FROM 
    @tbl ves
GROUP BY 
    ves.department_id
FOR JSON PATH;

The result

[
    {
        "department_id": 1,
        "count": 2,
        "employees": [
            {
                "first_name": "John",
                "last_name": "Doe",
                "employee_id": 1
            },
            {
                "first_name": "Foo",
                "last_name": "Bar",
                "employee_id": 2
            }
        ]
    },
    {
        "department_id": 2,
        "count": 1,
        "employees": [
            {
                "first_name": "some",
                "last_name": "more",
                "employee_id": 3
            }
        ]
    }
]

Answer 2

I did some research and found several example to transform relational data to JSON. Another way to achieve this result using FOR JSON AUTO:

SELECT DISTINCT
  ed.department_id,
  employees.id,
  employees.first_name,
  employees.last_name
FROM
  employees_departments ed
INNER JOIN
  employees_departments employees
ON
  ed.department_id = employees.department_id
ORDER BY ed.department_id, employees.id
FOR JSON AUTO;

Check the link below for more details.

https://www.mssqltips.com/sqlservertip/5348/advanced-techniques-to-transform-relational-data-to-json-in-sql-server-2016/