Microsoft Docs

SQL Server

FOR JSON Clause

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure Synapse Analytics

The FOR JSON clause formats query results as JSON text. It can be added to a SELECT statement. With FOR JSON, you can return query results from SQL Server tables and views in JavaScript Object Notation (JSON) format.

Syntax


SELECT columnList
FROM tableSource
[ WHERE filter ]
FOR JSON [ MODE ] [ , ROOT('rootName') ] [ , WITHOUT_ARRAY_WRAPPER ]

Parameters

JSON Output Modes

AUTO Mode

In AUTO mode, SQL Server automatically creates a JSON structure based on the structure of the selected columns. It attempts to infer relationships between tables and represent them in nested JSON objects.

Example: AUTO Mode


SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'USA'
FOR JSON AUTO;

This query would produce JSON like:


[
  {"CustomerID":"ALFKI","CompanyName":"Alfreds Futterkiste","ContactName":"Maria Anders"},
  {"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo"},
  ...
]

PATH Mode

PATH mode gives you explicit control over the JSON output structure. You can use dot notation in column aliases to create nested objects and arrays.

Example: PATH Mode with Nested Objects


SELECT
    c.CustomerID AS id,
    c.CompanyName AS company,
    (SELECT o.OrderID, o.OrderDate FROM Orders o WHERE o.CustomerID = c.CustomerID FOR JSON PATH) AS orders
FROM Customers c
WHERE c.Country = 'UK'
FOR JSON PATH;

This query produces JSON with nested orders for each customer:


[
  {
    "id": "AROUT",
    "company": "Around the Horn",
    "orders": [
      {"OrderID": 10707, "OrderDate": "2015-10-05T00:00:00"},
      ...
    ]
  },
  ...
]

You can also use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array.

Example: PATH Mode without Array Wrapper


SELECT TOP 1
    c.CompanyName AS company,
    c.ContactName AS contact
FROM Customers c
WHERE c.Country = 'Germany'
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Output:


{"company":"Berglunds snabbköp","contact":"Christina Berglund"}

RAW Mode

RAW mode returns each row from the query result as a separate JSON object. This is useful when you want a simple JSON array where each element is an object representing a row.

Example: RAW Mode


SELECT TOP 5 ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 100
FOR JSON RAW;

Output:


[
  {"ProductID":707,"Name":"Road-150","ListPrice":357.49},
  {"ProductID":708,"Name":"Road-150","ListPrice":357.49},
  ...
]

ROOT Clause

The ROOT('rootName') clause allows you to specify a name for the root element of the JSON output. This is particularly useful when you want to ensure that the JSON always has a specific top-level key.

Example: ROOT Clause


SELECT CustomerID, CompanyName
FROM Customers
WHERE Country = 'France'
FOR JSON PATH, ROOT('FrenchCustomers');

Output:


{
  "FrenchCustomers": [
    {"CustomerID": "BLAUS", "CompanyName": "Blauer See Delikatessen"},
    {"CustomerID": "BLONP", "CompanyName": "Blondes ddslkd"},
    ...
  ]
}

Considerations and Best Practices

Related Topics