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