Format Query Results as JSON with FOR JSON (SQL Server)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Format query results as JSON, or export data from SQL Server as JSON, by adding the FOR JSON clause to a SELECT statement. Use the FOR JSON clause to simplify client applications by delegating the formatting of JSON output from the app to SQL Server.

When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

  • To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.

  • To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

Here's an example of a SELECT statement with the FOR JSON clause and its output.

FOR JSON

Option 1 - You control output with FOR JSON PATH

In PATH mode, you can use the dot syntax – for example, 'Item.Price' – to format nested output.

Here's a sample query that uses PATH mode with the FOR JSON clause. The following example also uses the ROOT option to specify a named root element.

Diagram of flow of FOR JSON output

More info about FOR JSON PATH

For more detailed info and examples, see Format Nested JSON Output with PATH Mode (SQL Server).

For syntax and usage, see FOR Clause (Transact-SQL).

Option 2 - SELECT statement controls output with FOR JSON AUTO

In AUTO mode, the structure of the SELECT statement determines the format of the JSON output.

By default, null values are not included in the output. You can use the INCLUDE_NULL_VALUES to change this behavior.

Here's a sample query that uses AUTO mode with the FOR JSON clause.

Query:

SELECT name, surname  
FROM emp  
FOR JSON AUTO  

Results

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

More info about FOR JSON AUTO

For more detailed info and examples, see Format JSON Output Automatically with AUTO Mode (SQL Server).

For syntax and usage, see FOR Clause (Transact-SQL).

Control other JSON output options

Control the output of the FOR JSON clause by using the following additional options.

Output of the FOR JSON clause

The output of the FOR JSON clause has the following characteristics:

  1. The result set contains a single column.

    • A small result set may contain a single row.
    • A large result set splits the long JSON string across multiple rows.

      • By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.
      • Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

      Example of FOR JSON output

  2. The results are formatted as an array of JSON objects.

    • The number of elements in the JSON array is equal to the number of rows in the results of the SELECT statement (before the FOR JSON clause is applied).

    • Each row in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a separate JSON object in the array.

    • Each column in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a property of the JSON object.

  3. Both the names of columns and their values are escaped according to JSON syntax. For more info, see How FOR JSON escapes special characters and control characters (SQL Server).

Example

Here's an example that demonstrates how the FOR JSON clause formats the JSON output.

Query results

A B C D
10 11 12 X
20 21 22 Y
30 31 32 Z

JSON output

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}] 

For more info about what you see in the output of the FOR JSON clause, see the following topics.

Learn more about the built-in JSON support in SQL Server

For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

See Also

FOR Clause (Transact-SQL)
Use FOR JSON output in SQL Server and in client apps (SQL Server)