主页 / dba / 问题 / 297446
Accepted
HeyJude
Asked: 2021-08-02 00:47:45 +0800 CST

如何构造将 TOP 1 记录转换为 json 对象（而不是 json 数组）的 FOR JSON

  • 772

我想将 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}}
sql-server json

1 个回答

  1. Best Answer

    试试这个：

    
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}}
    • 0

相关问题