To maintain full control over the format of the JSON output, you need to specify the PATH option with the FOR JSON clause. The PATH mode lets you create wrapper objects and nest complex properties. The results are formatted as an array of JSON objects.

The FOR JSON PATH clause will use the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the FOR JSON PATH clause will create a nested object.

Assume you want to have more control over the output generated by FOR JSON AUTO in the previous subsection, and instead of a flat list of properties you want to represent EmailAddress and PhoneNumbers as nested properties of a new property named Contact. Here is the required output for the PersonID property with a value of 2:

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

To achieve this, you simply add an alias to columns that need to be nested. In the alias, you have to use a dot syntax, which defines a JSON path to the property. Here is the code that implements the previous request:

SELECT TOP (3) PersonID, FullName,  
EmailAddress AS 'Contact.Email', PhoneNumber AS 'Contact.Phone'  
FROM Application.People ORDER BY PersonID ASC FOR JSON PATH; 

Here is the expected result:

[ 
   { 
      "PersonID":1, 
      "FullName":"Data Conversion Only" 
   }, 
   { 
      "PersonID":2, 
      "FullName":"Kayla Woodcock", 
      "Contact":{ 
         "Email":"[email protected]", 
         "Phone":"(415) 555-0102" 
      } 
   }, 
   { 
      "PersonID":3, 
      "FullName":"Hudson Onslow", 
      "Contact":{ 
         "Email":"[email protected]", 
         "Phone":"(415) 555-0102" 
      } 
   } 
]

By default, null values are not included in the output as you can see in the first array element; it does not contain the Contact property.

FOR JSON PATH does not require a database table. The following statement, which was not allowed in the AUTO mode, works in the PATH mode:

SELECT GETDATE() AS today FOR JSON PATH; 

It returns:

[{"today":"2017-08-26T09:13:32.007"}]

If you reference more than one table in the query, the results are represented as a flat list, and then FOR JSON PATH nests each column using its alias. JSON PATH allows you to control generated JSON data and to create nested documents.