How to Format Query Results as JSON in MSSQL

One of the top features of MSSQL server 2016 is built in support of JSON. Yes, now we can format query results as JSON in MSSQL 2016 version onwards.

The data from the MSSQL can be exported to JSON by adding the FOR JSON clause into a SELECT statement. When we use the FOR JSON clause in the SELECT statement, we can specify the structure of the output explicitly, or let the structure of the SELECT statement determine the output.

• Use PATH mode with the FOR JSON clause.

  When you use PATH mode with the FOR JSON clause, you maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.

• Use AUTO mode with the FOR JSON clause.

  When you use AUTO mode with the FOR JSON clause, the JSON output is formatted automatically based on the structure of the SELECT statement.

Using PATH mode with the FOR JSON Clause.

To maintain full control over the format of the JSON output, specify the PATH option with the FOR JSON clause. PATH mode lets you create wrapper objects and nest complex properties.When you simply select rows from a table, the results are formatted as an array of JSON objects.If you query two or more tables, PATH mode returns flat results by default. Each column in the results becomes a property of the JSON object.

Format nested results by using dot-separated column names or by using nested queries, as shown in the following examples. By default, null values are not included in the output

SELECT SalesOrderNumber AS 'Order.Number',
       OrderDate AS 'Order.Date',
       UnitPrice AS 'Product.Price',
       OrderQty AS 'Product.Quantity'
FROM Sales.SalesOrderHeader H
  INNER JOIN Sales.SalesOrderDetail D
    ON H.SalesOrderID = D.SalesOrderID
FOR JSON PATH

Output of FOR JSON with Path mode

[
  {
    "Order":{
        "Number":"SO43659",
        "Date":"2011-05-31T00:00:00"
      },
    "Product":{
         "Price":2024.9940,
         "Quantity":1
     }
  },
  {
    "Order":{ "Number":"SO43659“ },
    "Product":{"Price":2024.9940}
  }
]

Use AUTO mode with the FOR JSON clause.

To format the JSON output automatically based on the structure of the SELECT statement, specify the AUTO option with the FOR JSON clause.A query that uses the FOR JSON AUTO option must have a FROM clause.With the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can’t change this format

SELECT SalesOrderNumber,
       OrderDate,
       UnitPrice,
       OrderQty
FROM Sales.SalesOrderHeader H
  INNER JOIN Sales.SalesOrderDetail D
    ON H.SalesOrderID = D.SalesOrderID
FOR JSON AUTO

Output of FOR JSON with Auto mode

[
  {
       "SalesOrderNumber":"SO43659",
        "OrderDate":"2011-05-31T00:00:00",
        "D":[
            {"UnitPrice":24.99, "OrderQty":1  }
         ]
  },
  {
       "SalesOrderNumber":"SO43659" ,
       "D":[
          { "UnitPrice":34.40  },
          { "UnitPrice":134.24, "OrderQty":5 }
        ]
  }
]

If you need the output with root node check out the article on How to add root node to JSON output in MSSQL