---
title: "Use FOR JSON Output in SQL Server and in Client Apps"
description: "Use FOR JSON output in SQL Server and in client apps (SQL Server)"
author: jovanpop-msft
ms.author: jovanpop
ms.reviewer: jroth
ms.date: 03/12/2025
ms.service: sql
ms.topic: language-reference
helpviewer_keywords:
  - "FOR JSON, using in client apps"
  - "FOR JSON, using in SQL Server"
monikerRange: "=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current"
---
# Use FOR JSON output in SQL Server and in client apps

[!INCLUDE [sqlserver2016-asdb-asdbmi-asa-serverless-pool-only](../../includes/applies-to-version/sqlserver2016-asdb-asdbmi-asa-serverless-pool-only.md)]

The following examples demonstrate some of the ways to use the `FOR JSON` clause and its JSON output in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or in client apps.  [Format query results as JSON with FOR JSON](format-query-results-as-json-with-for-json-sql-server.md)

In Fabric Data Warehouse, `FOR JSON` must be the last operator in the query, and so is not allowed inside subqueries, as in the examples in this article.

## Use FOR JSON output in SQL Server variables

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.  

```sql
DECLARE @x NVARCHAR(MAX) =
  (SELECT TOP 10 *
     FROM Sales.SalesOrderHeader
     FOR JSON AUTO)  
```  

## Use FOR JSON output in SQL Server user-defined functions

 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.  

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

```sql
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
```  

## Merge parent and child data into a single table

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.  

```sql
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  
```  

## Update the data in JSON columns

 The following example demonstrates that you can update the value of a column that contains JSON text.  

```sql
UPDATE SalesOrder  
SET Details =  
     (SELECT TOP 1 UnitPrice, OrderQty  
       FROM Sales.SalesOrderDetail D  
       WHERE D.SalesOrderId = SalesOrder.SalesOrderId  
      FOR JSON AUTO) 
```  

## Use FOR JSON output in a C# client app

 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.  

```sql
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());
            }
        }
    }
}
```  

## Learn more about JSON in the SQL Database Engine

### Microsoft videos

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

-   [JSON as a bridge between NoSQL and relational worlds](https://channel9.msdn.com/events/DataDriven-SQLServer2016/JSON-as-bridge-betwen-NoSQL-relational-worlds)

## Related content

- [Format Query Results as JSON with FOR JSON (SQL Server)](../../relational-databases/json/format-query-results-as-json-with-for-json-sql-server.md)