The default JSON output is enclosed within square brackets, which means the output is an array. If you want to format it as a single object instead of an array, use the WITHOUT_ARRAY_WRAPPER option.

Even if a query returns only one row, SQL Server will format it by default as a JSON array, as in the following example:

SELECT PersonID, FullName, EmailAddress, PhoneNumber  
FROM Application.People WHERE PersonID = 2 FOR JSON AUTO; 

Although only one row is returned, the output is still an array (with a single element):

[
  {
    "PersonID":2,
    "FullName":"Kayla Woodcock",
    "EmailAddress":"[email protected]",
    "PhoneNumber":"(415) 555-0102"
  }
]

To return a single object instead of an array, you can specify the WITHOUT_ARRAY_WRAPPER option:

SELECT PersonID, FullName, EmailAddress, PhoneNumber  
FROM Application.People WHERE PersonID = 2 FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER; 

The output looks more convenient now:

{
  "PersonID":2,
  "FullName":"Kayla Woodcock",
  "EmailAddress":"[email protected]",
  "PhoneNumber":"(415) 555-0102"
}

Removing square brackets from the output allows us to choose between an object and an array in the output JSON. However, only square brackets guarantee that the output is JSON conforming. Without the brackets, JSON text will be valid only if the underlined query returns a single row or no rows at all.

To demonstrate this, include PersonID with a value of 3 in your initial query:

SELECT PersonID, FullName, EmailAddress, PhoneNumber  
FROM Application.People WHERE PersonID IN (2, 3) FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER; 

The output is expected, but invalid; there is no parent object or array:

{
  "PersonID":2,
  "FullName":"Kayla Woodcock",
  "EmailAddress":"[email protected]",
  "PhoneNumber":"(415) 555-0102"
},
{
  "PersonID":3,
  "FullName":"Hudson Onslow",
  "EmailAddress":"[email protected]",
  "PhoneNumber":"(415) 555-0102"
}

But, wait! By specifying the ROOT option, you can wrap the output in an object, can't you? You saw this demonstrated earlier in this chapter. You can add a no-name root element to the preceding output:

SELECT PersonID, FullName, EmailAddress, PhoneNumber  
FROM Application.People WHERE PersonID IN (2, 3) FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER, ROOT(''); 

This should add a top-level element, and with that change, the JSON output should be valid. Check this out in the output:

Msg 13620, Level 16, State 1, Line 113
ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON. Remove one of these options.

A bitter disappointment! You cannot combine these two options! Therefore, use this option with caution; be aware that the JSON could be invalid.