Início / dba / Perguntas / 297446
Accepted
HeyJude
Asked: 2021-08-02 00:47:45 +0800 CST

Como construir um FOR JSON que converte um registro TOP 1 em um objeto json (em vez de um array json)

  • 772

Eu quero usar a JSON_QUERYcom uma SELECT TOP 1consulta de modo que o json resultante tenha esse registro top 1 em forma de objeto, em vez de em forma de tabela?

Por exemplo, a seguinte consulta ( demonstração ao vivo ):

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
;

saídas:

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

Mas eu quero:

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

1 respostas

  1. Best Answer

    Tente este :

    
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
;

    Resultado:

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

relate perguntas