I have 3 tables with 1-n relations between them
CREATE TABLE Province
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
-- ...
)
CREATE TABLE District
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
ProvinceId BIGINT REFERENCES [Province](Id)
)
CREATE TABLE Ward
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
DistrictId BIGINT REFERENCES [District](Id)
)
I want to get detail data of a province which looks like this
{
"id": 1,
"province_name": "province name",
"districts": [
{
"id": 1,
"district_name": "district-1",
"wards": [1,2,3]
},
{
"id": 2,
"district_name": "district-2",
"wards": [4,5]
}
]
}
(1 province has many districts, 1 district has many wards)
Here’s what I implemented using the STRING_AGG built-in function
select CONCAT(
'{"id":"', p.[id], '"',
',"province_name":', p.ProvinceName,
',"districts":',
CONCAT(
'[{',
STRING_AGG(
CONCAT('"id":', CAST(po.Id AS VARCHAR(10)),
',"name":"', po.[Name],
'","district_name":[', STRING_AGG(CAST(w.id AS VARCHAR(10)), ','), ']'), '},{'),
'}]'),
'}')
from
province p
left join
district d on d.provinceId = p.id
left join
ward w on w.districtId = d.id
where
p.id = @id
But SQL Server throws this exception:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery
I tried to search similar issue from this site, but couldn’t find expect results.
How can I implement to get data from 3 1-n layers?
11
This is impossible for demonstrate for the OP’s data, as we don’t have any. I’ve therefore used some sys objects to demonstrate how to:
- Nest JSON value
- Emulate
JSON_ARRAYAGG, as it’s not available outside of Preview.
JSON nesting is done just like XML nesting, with sub queries. For aggregating the values into an array, then one with is to still using STRING_AGG, however, we’ll then need to use JSON_QUERY to turn that into actual JSON, rather than a literal. This results in something like the following:
SELECT s.name AS schema_name,
(SELECT object_id,
name,
JSON_QUERY((SELECT CONCAT('[',STRING_AGG(c.column_id,','),']')
FROM sys.columns c
WHERE c.object_id = t.object_id),'$') AS column_ids
FROM sys.tables t
WHERE t.schema_id = s.schema_id
FOR JSON AUTO) AS tables
FROM sys.schemas s
FOR JSON AUTO;
And that gives a result like this:
[
{
"schema_name": "dbo"
},
{
"schema_name": "guest"
},
{
"schema_name": "INFORMATION_SCHEMA"
},
{
"schema_name": "sys"
},
{
"schema_name": "fn"
},
{
"schema_name": "sp"
},
{
"schema_name": "t"
},
{
"schema_name": "tbl",
"tables": [
{
"object_id": 389576426,
"name": "Calendar",
"column_ids": [
1,
2,
3,
4,
5,
6,
7,
8
]
},
{
"object_id": 1618104805,
"name": "Clock",
"column_ids": [
1,
2,
3,
4
]
}
]
}
]
If you are dealing with strings, then you will need to also quote the value, and likely want to use something like STRING_ESCAPE to ensure that certain characters are escaped appropriately:
CONCAT('[',STRING_AGG('"' + STRING_ESCAPE(YourColumn) + '"',','),']')
@Thom just beat me, but anyway, here’s the main concept:
CREATE TABLE Province
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
)
CREATE TABLE District
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
ProvinceId BIGINT
)
CREATE TABLE Ward
(
Id BIGINT PRIMARY KEY,
Name NVARCHAR(255),
DistrictId BIGINT
)
insert into Province
values (1,'test')
insert into District
values (1, 'distinct-1', 1)
, (2, 'distinct-2', 1)
insert into Ward
values (1, 'Ward1', 1)
, (2, 'Ward2', 1)
select id, name
, (
select id, name
, json_query((
select '[' + string_agg(ID, ',') within group(order by ID) + ']'
from Ward w
where w.DistrictId = d.ID
)) as wards
from district d
where d.ProvinceId = p.ID
for json path
) as districts
from province p
for json path, WITHOUT_ARRAY_WRAPPER
Only problem is that SQL Server doesn’t support pure arrays in JSON for some reason, otherwise you should be good to go.
One important wrinkle is that JSON_QUERY is needed to convert the “pseudo”-json array to a real JSON field. This is mostly SQL Server goo, otherwise it thinks the array is pure string, and will try to escape it, which we don’t want.
You can pre-group ward.Id, as shown in Cte.
Or use cross apply with concatenated ward.id’s
See example
declare @id int =1;
with wardList as(
select d.Id as DistrictId,string_agg(w.id,',') wList
from district d
left join ward w on w.DistrictId=d.Id
where d.ProvinceId=@id
group by d.id
)
select CONCAT(
'{"id":"', p.[id], '"',
',"province_name":', max(p.Name),
',"districts":',
CONCAT(
'[{',
STRING_AGG(
CONCAT('"id":', CAST(d.Id AS VARCHAR(10)),
',"district_name":"', d.Name,
'","wards":[', wList, ']'), '},{'),
'}]'),
'}')
from province p
left join district d on d.provinceId = p.id
left join wardList w on w.DistrictId = d.id
where p.id = @id
group by P.ID
Result is
{"id":"1","province_name":Province1,
"districts":[
{"id":1,"district_name":"P1District1","wards":[1,2,3]},
{"id":2,"district_name":"P1District2","wards":[4,5,6]},
{"id":3,"district_name":"P1District3","wards":[]}
]
}
Similar query
select CONCAT(
'{"id":"', p.[id], '"',
',"province_name":', max(p.Name),
',"districts":',
CONCAT(
'[{',
STRING_AGG(
CONCAT('"id":', CAST(d.Id AS VARCHAR(10)),
',"district_name":"', d.Name,
'","wards":[', wList, ']'), '},{'),
'}]'),
'}')
from province p
left join district d on d.provinceId = p.id
cross apply (
select string_agg(w.id,',') wList
from district d2
left join ward w on w.DistrictId=d.Id
where d2.ProvinceId=p.id and w.DistrictId=d2.Id
group by d2.id
) wardList
where p.id = @id
group by P.ID
Output is
{"id":"1","province_name":Province1
,"districts":[
{"id":1,"district_name":"P1District1","wards":[1,2,3]},
{"id":2,"district_name":"P1District2","wards":[4,5,6]}
]
}
With test data
insert into Province values
(1,'Province1')
,(2,'Province1')
insert into District values
(1, 'P1District1', 1)
,(2, 'P1District2', 1)
,(3, 'P1District3', 1)
,(4, 'P2District1', 2)
;
insert into Ward values
(1, 'P1D1Ward1', 1)
,(2, 'P1D1Ward2', 1)
,(3, 'P1D1Ward3', 1)
,(4, 'P1D2Ward1', 2)
,(5, 'P1D2Ward2', 2)
,(6, 'P1D2Ward3', 2)
;
fiddle