How to construct a FOR JSON that converts a TOP 1 record into a json object (instead of a json array)

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply