title: How to aggregate JSON in SQL Server?
body: <p>I looked into SQL Server documentation, but didn't find something like JSON aggregate. This is structure of my table.</p>
  
  <pre><code>| 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       |            |             |
  </code></pre>
  
  <p>What I am expecting is:</p>
  
  <pre><code>{
      department_id: 1,
      count: 2,
      employees: [
      {
          first_name: 'John',
          last_name: 'Doe'
          employee_id: 1
      },
      {
          first_name: 'Foo',
          last_name: 'Bar',
          employee_id: 2
      }]
  }
  </code></pre>
  
  <p>From this query I was able to get this result.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But I am wondering is there any better approach to aggregate JSON? just like <code>STRING_AGG()</code>?</p>
  
  <pre><code>SELECT
      department_id,
      COUNT(1) as count,
      STRING_AGG(employee_id, ',')
  FROM 
      employees_shifts 
  GROUP BY 
      department_id;
  </code></pre>