FOR JSON clause in SQL Server 2016 CTP2

One of the features that are coming in SQL Server 2016 CTP2 is JSON support. In this post I will explain what JSON functionalities will be available in CTP2 release. Note that this is just a first set of functions, others will come in CTP3.

In SQL Server 2016 CTP2 you will have FOR JSON clause. The main purpose of FOR JSON is to create new JSON objects.

Fig. 1. Formatting content of table as JSON object.

You just need to provide values of JSON as literals, expressions or column values, and specify property names using column names or aliases. FOR JSON will handle all formatting and escaping JSON special characters so you will get a valid JSON text as a result. As an example, you can execute some SELECT query and format values in the result set as JSON text using FOR JSON clause:

SELECT <<>>
FOR JSON PATH

You might use this clause in following scenarios:

  • Moving the logic for creating responses of JSON web services into database layer. You can generate parts of JSON responses by formatting queries directly in the database and return it back to app layer. This might improve performance of your web services, especially if app layer frameworks need to send several queries to fetch the data that should be formatted.
  • De-normalizing data. If you have complex table structures and you want to store (e.g. put all tags for a blog post in the Post table instead of the PostTags table), you can format related table as JSON array and store it into primary table as a regular column. 

You can find detailed documentation on MSDN in the Format Query Results as JSON with FOR JSON (SQL Server) section. Here, I will briefly explain how you can use this functionality. In SQL Server CTP2 will be available two FOR JSON clauses - FOR JSON PATH and FOR JSON AUTO. We will start with the FOR JSON PATH. 

FOR JSON PATH

FOR JSON PATH is a new clause (although similar to the existing FOR XML PATH) that creates JSON object using a set of (column name: cell value) pairs from T-SQL queries.
In the simplest example, you can define some text variable and put JSON text as a content of that variable:

 DECLARE @json as NVARCHAR(MAX)
SET @json = (SELECT 'John' as Name, 'Doe' as Surname,
                     35 as DoB, getdate() as DateCreated
                     FOR JSON PATH)

 FOR JSON PATH clause will take Name:John, Surname:Doe, DoB:35, and DateCreated:getdate() pairs and format them as key:value pairs of JSON object:

 {
 "Name":"John",
 "Surname":"Doe",
 "DoB":35,
 "DateCreated": "2015-06-25T24:31:12.0173"
}

FOR JSON will convert values of SQL types to JSON literals and escape special characters according to the JSON specification, so  you don't need to worry about this.

Why it is called FOR JSON PATH?

In many cases you would need to generate hierarchical structure of JSON documents instead of flat JSON text. FOR JSON PATH enables you to define JSON paths for each generated property. As an example, imagine that you would like to put name and surname from the previous example within the same "Info" object. You can change query above and specify path where Name and Surname should be placed: 

 SET @json = (SELECT 'John' as [Info.Name],
                    'Doe' as 'Info.Surname',
                    35 as DoB, getdate() as DateCreated  
                    FOR JSON PATH)

 Info.Name specifies that value 'John' should be generated in the object Info as property 'Name'. Since dot (.) is used as separator for JSON paths, you should encapsulate these aliases in brackets ( [ and ] ) or apostrophes ('). As a result you will have the following JSON:

 {
 "Info": {
     "Name":"John",
     "Surname":"Doe"
 },
 "DoB":35,
 "DateCreated": "2015-06-25T24:31:12.0173"
}

In the example above, Name and Surname will be placed inside the Info object because their paths for these values are "Info.Name" and "Info.Surname". FOR JSON will find that we have two identical prefixes in Info.Name and Info.Surname so they will be wrapped with the same parent object.

If you provide just values in the single "row" in SELECT clause, FOR JSON PATH will generate single JSON object. However, you can apply FOR JSON PATH clause on a query that returns a set of results from some table, e.g.:

 SELECT SalesOrderNumber as [Order.Number],
 OrderDate as 'Order.Date',
 AccountNumber
 FROM Sales.SalesOrderHeader
 FOR JSON PATH

In this case, FOR JSON PATH clause will see that result has multiple rows, so instead of a single object, it will generate an array of objects. Each row will be generated as separate JSON object in the array. Standard path nesting, type conversion, and escaping will be applied on each of the objects. Result of the query might be:

 [
 {
 "Order":{"Number":"SO43659","Date":"2011-05-31T00:00:00"},
 "AccountNumber":"10-4020-000676"
 },
 {
 "Order":{"Number":"SO43660","Date":"2011-05-31T00:00:00"},
 "AccountNumber":"10-4020-000117"
 },
 {
 "Order":{"Number":"SO43661","Date":"2011-05-31T00:00:00"},
 "AccountNumber":"10-4020-000442"
 }
]

 If you don't want to use paths or you need more complex structure that cannot be specified using the paths in column aliases, you can use FOR JSON path in sub-queries, e.g.:

 SELECT SalesOrderNumber as [Order.Number],
 OrderDate as [Order.Date],
 AccountNumber,
 (SELECT OrderQty, UnitPrice FROM SalesOrderDetail
     WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
     FOR JSON PATH) AS Items
 FROM SalesOrderHeader
 FOR JSON PATH

Every FOR JSON sub-query will produce a separate JSON or array of JSON objects that will be included in the main JSON object. This way you have a full control over JSON formatting.

 [
 {
 "Order":{"Number":"SO43659","Date":"2011-05-31T00:00:00"},
 "AccountNumber":"10-4020-000676",
 "Items":[{"OrderQty":1,"UnitPrice":2024.9940},
 {"OrderQty":3,"UnitPrice":2024.9940}]
 },
 {
 "Order":{"Number":"SO43660","Date":"2011-05-31T00:00:00"},
 "AccountNumber":"10-4020-000117"},
 "Items":[{"OrderQty":3,"UnitPrice":1024.9940},
 {"OrderQty":2,"UnitPrice":1024.9940}]
 }
]

You can find more examples in Format Nested JSON Output with PATH Mode (SQL Server) page.

Alternative - FOR JSON AUTO

FOR JSON AUTO clause automatically formats JSON based on the order of columns from the tables. You don't need to explicitly specify paths in the column aliases or to use nested sub-queries. First column in the SELECT list will be used as property of the first-level objects in the JSON array. When a column from some other table is found it will be automatically formatted as nested object within the first-level object. In the following example you can see rewritten previous query where I have used AUTO instead of the nested sub-query:

 SELECT SalesOrderNumber as [Order.Number],
 OrderDate as [Order.Date],
 AccountNumber,
 OrderQty, UnitPrice
 FROM Sales.SalesOrderHeader
 JOIN Sales.SalesOrderDetail as Items
 ON Sales.SalesOrderHeader.SalesOrderID = Items.SalesOrderID
 FOR JSON AUTO

The output is the same - FOR JSON AUTO will place first three columns as first-level properties, while OrderQty and UnitPrice will be placed in the nested sub-array named "Items" (note that it will use table name or table alias as a name of sub-array). You can find more examples on Format JSON Output Automatically with AUTO Mode (SQL Server)

Note that FOR JSON AUTO works only with table sources. You cannot create single JSON object using this clause and SELECT query without table source. For this simpler case you should use FOR JSON PATH.

Utilities - ROOT an INCLUDE_NULL_VALUES

Two additional options that might be useful for you are ROOT and INCLUDE_NULL_VALUES options.

By default, NULL cells and values will not be generated in the JSON output (these properties will be skipped). If you want NULL values to be generated you can require this using the INCLUDE_NULL_VALUES option - you can find more details about this option on Include Null Values in JSON Output with the INCLUDE_NULL_VALUES Option (SQL Server).

ROOT option enables you to specify single wrapper object around the generated JSON object  - you can find more details about this option on  Add a Root Node to JSON Output with the ROOT Option (SQL Server).

Both options can be used with FOR JSON PATH an FOR JSON AUTO clauses.

FOR JSON - Getting Started.sql