I have a query that dynamically builds a SELECT statement in SQL, and outputs as per the example below (note there can be as many as 50 select statements, each unique per email);
Age | Gender | |
---|---|---|
[email protected] | 96 | Male |
Age | AddressLine1 | Country | |
---|---|---|---|
[email protected] | 36 | 1 Laneview Way | England |
I would like to combine the output of the two (or more) queries, into one JSON object, if possible, so it would output like below;
[
{
"Email":"[email protected]",
"Age":"96",
"Gender":"Male"
},
{
"Email":"[email protected]",
"Age":"36",
"AddressLine1":"1 Laneview Way",
"Country":"England"
}
]
My current attempt in SQL is as follows;
SELECT User_Alias = JSON_QUERY(query1.json_result), User_Alias2 = JSON_QUERY(query2.json_result) FROM
(
SELECT Email,
Age,
Gender
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
) AS query1 (json_result)
,(
SELECT Email,
Age,
AddressLine1,
Country
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
) AS query2 (json_result)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
It was written in two ways with two values and one value
Two values(User_Alias,User_Alias2)
select User_Alias = (
SELECT Email,
Age,
Gender
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
),User_Alias2 =
(
SELECT Email,
Age,
AddressLine1,
Country
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
one values
select CONCAT( (
SELECT Email,
Age,
Gender
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
),
(
SELECT Email,
Age,
AddressLine1,
Country
FROM tableAbc
WHERE Email = '[email protected]'
FOR JSON PATH
) )
dbfiddle:https://dbfiddle.uk/tZaEDwIE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With