Mastering SQL Server 2017

Add a root node to JSON output

By specifying the ROOT option in the FOR JSON query, you can add a single, top-level element to the JSON output. The following code shows this:

SELECT TOP (3) PersonID, FullName, EmailAddress, PhoneNumber 
FROM Application.People ORDER BY PersonID ASC FOR JSON AUTO, ROOT('Persons'); 

Here is the result:

{ 
   "Persons":[ 
      { 
         "PersonID":1, 
         "FullName":"Data Conversion Only"

      }, 
      { 
         "PersonID":2, 
         "FullName":"Kayla Woodcock", 
         "EmailAddress":"[e-mail]", 
         "PhoneNumber":"[phone]" 
      }, 
      { 
         "PersonID":3, 
         "FullName":"Hudson Onslow", 
         "EmailAddress":"[e-mail]", 
         "PhoneNumber":"[phone]" 
      } 
   ] 
} 

By specifying the root element, you have converted the outer array to a single complex ...