Mastering SQL Server 2017

FOR JSON AUTO

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