1

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 Aug 1 '21 at 19:42
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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.