Retrieving SQL Server data in the JSON format

This section explores JSON support in SQL Server with a very common action: formatting tabular data as JSON. In SQL Server 2016, the clause FOR JSON can be used with the SELECT statement to accomplish this. It is analogous to formatting relational data as XML by using the FOR XML extension.

When you use the FOR JSON clause, you can choose between two modes:

• FOR JSON AUTO: The JSON output will be formatted by the structure of the SELECT statement automatically.

• FOR JSON PATH: The JSON output will be formatted by the structure explicitly defined by you. With JSON PATH, you can create a more complex output (nested objects and properties).

In both modes, SQL Server extracts relational data defined by the SELECT statement, converts SQL Server data types to appropriate JSON types, implements escaping rules, and finally formats the output according to explicitly or implicitly defined formatting rules.

FOR JSON AUTO

Use FOR JSON AUTO when you want to let...