Use FOR JSON output in SQL Server and in client apps (SQL Server)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (serverless SQL pool only)
The following examples demonstrate some of the ways to use the FOR JSON clause and its JSON output in SQL Server or in client apps.
The output of the FOR JSON clause is of type NVARCHAR(MAX), so you can assign it to any variable, as shown in the following example.
DECLARE @x NVARCHAR(MAX) =
(SELECT TOP 10 *
FROM Sales.SalesOrderHeader
FOR JSON AUTO)
You can create user-defined functions that format result sets as JSON and return this JSON output. The following example creates a user-defined function that fetches some sales order detail rows and formats them as a JSON array.
CREATE FUNCTION GetSalesOrderDetails(@salesOrderId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (SELECT UnitPrice, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @salesOrderId
FOR JSON AUTO)
END
You can use this function in a batch or query, as shown in the following example.
DECLARE @x NVARCHAR(MAX) = dbo.GetSalesOrderDetails(43659)
PRINT dbo.GetSalesOrderDetails(43659)
SELECT TOP 10
H.*, dbo.GetSalesOrderDetails(H.SalesOrderId) AS Details
FROM Sales.SalesOrderHeader H
In the following example, each set of child rows is formatted as a JSON array. The JSON array becomes the value of the Details column in the parent table.
SELECT TOP 10 SalesOrderId, OrderDate,
(SELECT TOP 3 UnitPrice, OrderQty
FROM Sales.SalesOrderDetail D
WHERE H.SalesOrderId = D.SalesOrderID
FOR JSON AUTO) AS Details
INTO SalesOrder
FROM Sales.SalesOrderHeader H
The following example demonstrates that you can update the value of a column that contains JSON text.
UPDATE SalesOrder
SET Details =
(SELECT TOP 1 UnitPrice, OrderQty
FROM Sales.SalesOrderDetail D
WHERE D.SalesOrderId = SalesOrder.SalesOrderId
FOR JSON AUTO)
The following example shows how to retrieve the JSON output of a query into a StringBuilder object in a C# client app. Assume that the variable queryWithForJson
contains the text of a SELECT statement with a FOR JSON clause.
var queryWithForJson = "SELECT ... FOR JSON";
using(var conn = new SqlConnection("<connection string>"))
{
using(var cmd = new SqlCommand(queryWithForJson, conn))
{
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
jsonResult.Append("[]");
}
else
{
while (reader.Read())
{
jsonResult.Append(reader.GetValue(0).ToString());
}
}
}
}
Note
Some of the video links in this section may not work at this time. Microsoft is migrating content formerly on Channel 9 to a new platform. We will update the links as the videos are migrated to the new platform.
For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos: