當join不相關時，基於同一張表上的多個查詢創建一個json

我需要根據同一張表上兩個不同查詢的結果構造一個 json，其中連接是無關緊要的。

考慮以下範例（此處為完整展示）：

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

我想建構以下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
    }
 ]
}
 

我試過這個：

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;

但顯然這不是要走的路，因為它給出了這個：

[
 {
   "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
   }
 }
]

我怎樣才能達到預期的效果？

在您預期的 JSON 中，highTrees並且lowTrees是鍵。以及您通常從列中獲取的鍵。因此，這些應該是生成查詢中的單獨列，而不是同一行集中的單獨行子集。

知道了這一點，您可以UNION像這樣修改您的查詢（現場展示）：

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
;

並獲得（幾乎）預期的輸出：

{
 "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
   }
 ]
}

引用自：https://dba.stackexchange.com/questions/296307