1

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?

1 Answer 1

2

In your expected JSON, highTrees and lowTrees are keys. And keys you typically obtain from columns. Therefore, those should be separate columns in the generating query, rather than separate subsets of rows in the same row set.

Knowing that, you could modify your UNION query like this (live demo):

SELECT
  highTrees = JSON_QUERY(
                (
                  SELECT 
                  Id as id,
                  Type as type,
                  Height as height
                  FROM Trees WHERE [Height] > 5
                  FOR JSON PATH
                )
              ),

  lowTrees  = JSON_QUERY(
                (
                  SELECT 
                  Id as id,
                  Type as type,
                  Height as height
                  FROM Trees WHERE [Height] < 1
                  FOR JSON PATH
                )
              )
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER
;

and get (almost) the expected output:

{
  "highTrees": [
    {
      "id": 1,
      "type": "Palm",
      "height": 5.5
    },
    {
      "id": 2,
      "type": "Pine",
      "height": 6.2
    }
  ],
  "lowTrees": [
    {
      "id": 4,
      "type": "Japanese Cedar",
      "height": 0.5
    },
    {
      "id": 5,
      "type": "Spanish Fir",
      "height": 0.6
    }
  ]
}
2
  • Thank you! and of course, I had a typo in my json, I'll just fix it in my question.
    – HeyJude
    Commented Jul 28, 2021 at 20:16
  • 1
    @HeyJude: Cool, I've removed the now redundant part of my explanation as well.
    – Andriy M
    Commented Jul 28, 2021 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.