如何構造將 TOP 1 記錄轉換為 json 對象（而不是 json 數組）的 FOR JSON

我想將 aJSON_QUERY與SELECT TOP 1查詢一起使用，以便生成的 json 以對象形式而不是表形式具有前 1 條記錄？

例如，以下查詢（現場展示）：

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


SELECT
 highestTree = JSON_QUERY(
     (
        SELECT TOP 1
        Id as id,
        Type as type,
        Height as height
        FROM Trees 
        WHERE Height = (SELECT Max(Height) FROM Trees)
        FOR JSON PATH
     )
 ),
 lowestTree  = JSON_QUERY(
     (
        SELECT TOP 1
        Id as id,
        Type as type,
        Height as height
        FROM Trees 
        WHERE Height = (SELECT MIN(Height) FROM Trees)
        FOR JSON PATH
     )
)
FOR JSON
 PATH, WITHOUT_ARRAY_WRAPPER
;

輸出：

{"highestTree":[{"id":2,"type":"Pine","height":6.2}],"lowestTree":[{"id":4,"type":"Japanese Cedar","height":0.5}]}

但我想要：

{"highestTree":{"id":2,"type":"Pine","height":6.2},"lowestTree":{"id":4,"type":"Japanese Cedar","height":0.5}}

試試這個：

SELECT
 highestTree = JSON_QUERY(
     (
        SELECT TOP 1
        Id as id,
        Type as type,
        Height as height
        FROM Trees 
        WHERE Height = (SELECT Max(Height) FROM Trees)
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER
     )
 ),
 lowestTree = JSON_QUERY(
     (
        SELECT TOP 1
        Id as id,
        Type as type,
        Height as height
        FROM Trees 
        WHERE Height = (SELECT MIN(Height) FROM Trees)
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER
     )
)
FOR JSON
 PATH, WITHOUT_ARRAY_WRAPPER
;

結果：

{"highestTree":{"id":2,"type":"Pine","height":6.2},"lowestTree":{"id":4,"type":"Japanese Cedar","height":0.5}}

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