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;

Not that I'm aware of. You could move the correlated subquery to cross apply but I don't think that's going to change anything. — Zohar Peled
– Zohar Peled, Commented Apr 8, 2019 at 5:57
No, this approach with correlated sub-queries is the correct way to solve nested JSON issues (same with nested XML). Anyway, using string methods might lead into troubles, when it comes to characters which need escaping (e.g. the quotes in JSON). Talking about how to do things I'd suggest to separate your data to a normalised n:m structure (department, employees and a mapping table in between). — Gottfried Lesigang
– Gottfried Lesigang, Commented Apr 8, 2019 at 6:45
It's already normalized I have separate departments, employees table. It's a view I'm querying on. Could you elaborate it more? — Sagar Chamling
– Sagar Chamling, Commented Apr 8, 2019 at 7:06

Gottfried Lesigang · Accepted Answer · 2019-04-08 09:14:00Z

7

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

answered Apr 8, 2019 at 9:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sagar Chamling Over a year ago

It's not helpful.

Gottfried Lesigang Over a year ago

@SagarChamling now it's on you to elaborate more. What does not helpful mean?

Emaborsa Over a year ago

@Shungo Maybe because it is not exactly the same. What if the grouping is department_id, first_name? In Postgres we had a join with a group by and simply json_agg(). How is it in MSSQL? I mean, if we have two tables.

Sagar Chamling · Accepted Answer · 2019-04-13 16:18:59Z

1

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/

answered Apr 13, 2019 at 16:18

Sagar Chamling

1,0682 gold badges12 silver badges27 bronze badges

Comments

Jeremy Caney · Accepted Answer · 2022-09-08 00:46:42Z

0

We can get result same result with STRING_AGG and CONCAT function without subquery

SELECT   e.department_id,
         Count(e.department_id),
         Concat('[',String_agg(Concat('{first_name:"', e.last_name,
         '",last_name:"',e.last_name,'"}',
         '",employee_id:"',e.employee_id,'"}'),','),']') AS employees

FROM     employees e
JOIN     employee_departments ed
ON       e.employee_id=ed.employee_id
GROUP BY ed.department_id FOR json path,
         root('root')

edited Sep 8, 2022 at 0:46

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered Sep 3, 2022 at 10:40

shashikant rajak

11 bronze badge

2 Comments

shashikant rajak Over a year ago

Error "STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation" can be removed with STRING_AGG(cast(concat('','') as NVARCHAR(MAX))

icyerasor Apr 9 at 14:55

And there's a typo that returns {first_name:"', e.last_name - manually concating json will introduce bugs like this i guess.

Collectives™ on Stack Overflow

How to aggregate JSON in SQL Server?

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related