Use FOR JSON AUTO when you want to let SQL Server format query results for you. When you specify this mode, the JSON format is controlled by how the SELECT statement is written.

FOR JSON AUTO requires a table; you cannot use it without a database table or view. For instance, the following query will fail:

SELECT GETDATE() AS today FOR JSON AUTO; 

Here is the error message:

Msg 13600, Level 16, State 1, Line 13
FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

To demonstrate how SQL Server automatically generates JSON data, use the WideWorldImporters SQL Server 2017 sample database. Consider the following query, which returns the first three rows from the Application.People table:

USE WideWorldImporters; 
SELECT TOP (3) PersonID, FullName, EmailAddress, PhoneNumber 
FROM Application.People ORDER BY PersonID ASC; 

Here is the result in tabular format:

First, you will recall how SQL Server converts this data automatically to XML. To generate an XML, you can use the FOR JSON AUTO extension:

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

Here is the portion of XML generated by the previous query:

<Application.People PersonID="1" FullName="Data Conversion Only" />
<Application.People PersonID="2" FullName="Kayla Woodcock" EmailAddress="[email protected]" PhoneNumber="(415) 555-0102" />
<Application.People PersonID="3" FullName="Hudson Onslow" EmailAddress="[email protected]" PhoneNumber="(415) 555-0102" />

Analogous to this, the simplest way to convert the result in JSON format is to put the FOR JSON AUTO extension at the end of the query:

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

The result is an automatically formatted JSON text. By default, it is a JSON array with objects:

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

As you can see, in SQL Server Management Studio (SSMS), the JSON result is prepared in a single line. This is hard to follow and observe from a human-readable point of view. Therefore, you will need a JSON formatter. In this book, JSON output generated in SSMS is formatted by using the JSON formatter and validator that are available at https://jsonformatter.curiousconcept.com. The previous result looks better after additional formatting:

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

As you can see, for each row from the original result set, one JSON object with a flat property structure is generated. Compared to XML, you see less text since the table name does not appear in the JSON output.

The difference in size is significant when you compare JSON with XML generated by using the ELEMENTS option instead of default RAW. To illustrate this, you can use the following code; it compares the data length (in bytes) of XML-and JSON-generated output for all rows in the Sales.Orders table:

USE WideWorldImporters; 
SELECT  
  DATALENGTH(CAST((SELECT * FROM Sales.Orders FOR XML AUTO) AS    NVARCHAR(MAX))) AS xml_raw_size,  DATALENGTH(CAST((SELECT * FROM Sales.Orders FOR XML AUTO,    ELEMENTS) AS NVARCHAR(MAX))) AS xml_elements_size,  DATALENGTH(CAST((SELECT * FROM Sales.Orders FOR JSON AUTO) AS    NVARCHAR(MAX))) AS json_size;

The preceding query generates the following results:

xml_raw_size         xml_elements_size    json_size
-------------------- -------------------- --------------------
49161702             81161852             49149364

You can see that the XML representation of data when columns are expressed as XML elements is about 65% larger than the JSON representation. When they are expressed as XML attributes, JSON and XML output have approximately the same size.

The FOR JSON AUTO extension creates a flat structure with single-level properties. If you are not satisfied with the automatically created output and want to create a more complex structure, you should use the FOR JSON PATH extension.