Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    It looks like what you should actually be doing is just using NULL for the columns you don’t want to show. Any null columns are simply not serialized into the JSON at all, unless you specify INCLUDE_NULL_VALUES.

    SELECT Email,
       Age,
       Gender,
       AddressLine1,
       Country
FROM tableAbc
WHERE Email IN ('[email protected]', '[email protected]')
FOR JSON PATH;

    This assumes those columns are already null for those rows. If they are not then you need some CASE expressions

    SELECT Email,
       Age,
       Gender = CASE WHEN Email = '[email protected]' THEN Gender END,
       AddressLine1 = CASE WHEN Email = '[email protected]' THEN AddressLine1 END,
       Country = CASE WHEN Email = '[email protected]' THEN Country END
FROM tableAbc
WHERE Email IN ('[email protected]', '[email protected]')
FOR JSON PATH;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search