Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Managed Instance
Azure Synapse Analytics (serverless SQL pool only)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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.
Note
Azure Data Studio is the recommended query editor for JSON queries because it auto-formats the JSON results, as shown in this article. SQL Server Management Studio displays an unformatted string.
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.
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.
For more detailed info and examples, see Format Nested JSON Output with PATH Mode (SQL Server).
For syntax and usage, see SELECT - FOR Clause (Transact-SQL).
Control the output of the FOR JSON
clause, using the following extra options.
ROOT
To add a single, top-level element to the JSON output, specify the ROOT
option. If you don't specify this option, the JSON output doesn't have a root element. For more info, see Add a Root Node to JSON Output with the ROOT Option (SQL Server).
INCLUDE_NULL_VALUES
To include null values in the JSON output, specify the INCLUDE_NULL_VALUES
option. If you don't specify this option, the output doesn't include JSON properties for NULL
values in the query results. For more info, see Include Null Values in JSON - INCLUDE_NULL_VALUES Option.
WITHOUT_ARRAY_WRAPPER
To remove the square brackets that surround the JSON output of the FOR JSON
clause by default, specify the WITHOUT_ARRAY_WRAPPER
option. Use this option to generate a single JSON object as output from a single-row result. If you don't specify this option, the JSON output is formatted as an array - that is, the output is enclosed within square brackets. For more info, see Remove Square Brackets from JSON - WITHOUT_ARRAY_WRAPPER Option.
The output of the FOR JSON
clause has the following characteristics:
The result set contains a single column.
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 might 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.
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.
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).
Here's an example that demonstrates how the FOR JSON
clause formats the JSON output.
A | B | C | D |
---|---|---|---|
10 | 11 | 12 | X |
20 | 21 | 22 | Y |
30 | 31 | 32 | Z |
[{
"A": 10,
"B": 11,
"C": 12,
"D": "X"
}, {
"A": 20,
"B": 21,
"C": 22,
"D": "Y"
}, {
"A": 30,
"B": 31,
"C": 32,
"D": "Z"
}]
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Documentation
Solve common issues with JSON in SQL Server - SQL Server
Solve common issues with JSON in SQL Server
Include Null Values in JSON - INCLUDE_NULL_VALUES Option - SQL Server
Include Null Values in JSON - INCLUDE_NULL_VALUES Option
Work with JSON data in SQL Server - SQL Server
Combine NoSQL and relational concepts in the same database with JSON data in SQL Server