Prevent SQL "FOR JSON" from causing duplication

Question

I have a master table Departments. The details table Employees has foreign key pointing back to Departments table. When the simple join query is returned as JSON using the PATH option, it is listing multiple rows for the Department. Whereas when using the AUTO option, it is returning unique departments, but I loose the control of the schema. How can I use the PATH option and still be able to return unique departments just like the AUTO option. Here is the code:

Declare @Departments as table (DeptName varchar(100), Location varchar(100) )
insert @Departments 
select 'IT', 'San Francisco'
union
select 'Sales', 'Miami'
union
select 'Finance', 'NYC'

Declare @Employees as table (DeptName varchar(100) , EmployeeName varchar(100), Salary Decimal(7,2))
insert @Employees 
select 'Finance', 'Ponzi', 1000
union
select 'Finance', 'Madoff', 10000
union
select 'IT' , 'Bill', 20000
union
select 'IT', 'Steve', 100

select D.DeptName [Department.Name], D.Location [Department.Location], E.EmployeeName [Employee.Name], E.Salary [Employee.Salary]
from
    @Departments D
    left join @Employees E on E.DeptName = D.DeptName
for JSON Auto

The Auto mode returns the following result. Notice each department appearing only once:

[{
        "Department.Name": "Finance",
        "Department.Location": "NYC",
        "E": [{
                "Employee.Name": "Madoff",
                "Employee.Salary": 10000.00
            }, {
                "Employee.Name": "Ponzi",
                "Employee.Salary": 1000.00
            }
        ]
    }, {
        "Department.Name": "IT",
        "Department.Location": "San Francisco",
        "E": [{
                "Employee.Name": "Bill",
                "Employee.Salary": 20000.00
            }, {
                "Employee.Name": "Steve",
                "Employee.Salary": 100.00
            }
        ]
    }, {
        "Department.Name": "Sales",
        "Department.Location": "Miami",
        "E": [{}
        ]
    }
]

The PATH option returns the following results. Notice the multiple occurrences of each department:

[{
        "Department": {
            "Name": "Finance",
            "Location": "NYC"
        },
        "Employee": {
            "Name": "Madoff",
            "Salary": 10000.00
        }
    }, {
        "Department": {
            "Name": "Finance",
            "Location": "NYC"
        },
        "Employee": {
            "Name": "Ponzi",
            "Salary": 1000.00
        }
    }, {
        "Department": {
            "Name": "IT",
            "Location": "San Francisco"
        },
        "Employee": {
            "Name": "Bill",
            "Salary": 20000.00
        }
    }, {
        "Department": {
            "Name": "IT",
            "Location": "San Francisco"
        },
        "Employee": {
            "Name": "Steve",
            "Salary": 100.00
        }
    }, {
        "Department": {
            "Name": "Sales",
            "Location": "Miami"
        }
    }
]

How can the multiple occurrences of departments be prevented when using the PATH mode?

Aamir · Accepted Answer · 2017-07-03T14:24:39.070

Never mind. Had to modify the source query by JSONifying the Employees table first and then cross apply with Departments table and then JSONified the whole thing again at the end.

Query:

Declare @Departments as table (DeptName varchar(100), Location varchar(100) )
insert @Departments 
select 'IT', 'San Francisco'
union
select 'Sales', 'Miami'
union
select 'Finance', 'NYC'

Declare @Employees as table (DeptName varchar(100) , EmployeeName varchar(100), Salary Decimal(7,2))
insert @Employees 
select 'Finance', 'Ponzi', 1000
union
select 'Finance', 'Madoff', 10000
union
select 'IT' , 'Bill', 20000
union
select 'IT', 'Steve', 100

select D.DeptName [Department.Name], D.Location [Department.Location], jsonEmployees.Employees
from
    @Departments D
    cross apply (
        select EmployeeName [Employee.Name], Salary [Employee.Salary]
        from @Employees Employee 
        where Employee.DeptName = D.DeptName 
        For JSON path
    ) JsonEmployees(Employees)

for JSON path

Result:

[{
        "Department": {
            "Name": "Finance",
            "Location": "NYC"
        },
        "Employees": [{
                "Employee": {
                    "Name": "Madoff",
                    "Salary": 10000.00
                }
            }, {
                "Employee": {
                    "Name": "Ponzi",
                    "Salary": 1000.00
                }
            }
        ]
    }, {
        "Department": {
            "Name": "IT",
            "Location": "San Francisco"
        },
        "Employees": [{
                "Employee": {
                    "Name": "Bill",
                    "Salary": 20000.00
                }
            }, {
                "Employee": {
                    "Name": "Steve",
                    "Salary": 100.00
                }
            }
        ]
    }, {
        "Department": {
            "Name": "Sales",
            "Location": "Miami"
        }
    }
]

Prevent SQL "FOR JSON" from causing duplication

1 Answers1