I need to construct a json based on the results of two different queries on the same table, where a join is irrelevant.
Consider the following example (full demo is here):
CREATE TABLE Trees
(
(Id) INT,
(Type) NVARCHAR(100),
(Height) DECIMAL(2,1)
);
INSERT INTO Trees ((Id), (Type), (Height))
VALUES
(1, 'Palm', 5.5),
(2, 'Pine', 6.2),
(3, 'Apple', 2.5),
(4, 'Japanese Cedar', 0.5),
(5, 'Spanish Fir', 0.6);
I want to construct the following json:
(
"highTrees":
(
{
"id": 1,
"type": "Palm",
"height": 5.5
},
{
"id": 1,
"type": "Pine",
"height": 6.2
}
),
"lowTrees":
(
{
"id": 4,
"type": "Japanese Cedar",
"height": 0.5
},
{
"id": 5,
"type": "Spanish Fir",
"height": 0.6
}
)
)
I tried this:
SELECT
Id as 'highTrees.id',
Type as 'highTrees.type',
Height as 'highTrees.height'
FROM Trees WHERE (Height) > 5
UNION ALL
SELECT
Id as 'lowTrees.id',
Type as 'lowTrees.type',
Height as 'lowTrees.height'
FROM Trees WHERE (Height) < 1
FOR JSON PATH;
But obviously this isn’t the way to go, since it gives this:
(
{
"highTrees": {
"id": 1,
"type": "Palm",
"height": 5.5
}
},
{
"highTrees": {
"id": 2,
"type": "Pine",
"height": 6.2
}
},
{
"highTrees": {
"id": 4,
"type": "Japanese Cedar",
"height": 0.5
}
},
{
"highTrees": {
"id": 5,
"type": "Spanish Fir",
"height": 0.6
}
}
)
How can I achieve the desired result?
