2

I want to use a JSON_QUERY with a SELECT TOP 1 query such that the resultant json has that top 1 record in object form, instead of in table form?

For example, the following query (live demo):

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
;

outputs:

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

But I want:

{"highestTree":{"id":2,"type":"Pine","height":6.2},"lowestTree":{"id":4,"type":"Japanese Cedar","height":0.5}}
1
  • 1
    Side note: Instead of that subquery WHERE Height = (SELECT Max(Height) FROM Trees), just do ORDER BY Height DESC and for lowest do ORDER BY Height ASC Commented Aug 1, 2021 at 19:42

1 Answer 1

0

Try this one :


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
;

Result:

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

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.