Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combining two SQL results (with varying columns) into one JSON object

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);

Email Age Gender
[email protected] 96 Male
Email 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
like image 883
dawsonz Avatar asked Sep 18 '25 08:09

dawsonz


1 Answers

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

like image 84
abolfazl sadeghi Avatar answered Sep 20 '25 23:09

abolfazl sadeghi