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

Comments

• Anonymous
  June 08, 2015
  The comment has been removed

• Anonymous
  June 08, 2015
  Grammar: "One of the features that is coming in SQL Server 2016 CTP2 is JSON support." Should be: One of the features that are coming in SQL Server 2016 CTP2 is JSON support.

• Anonymous
  July 07, 2015
  Really nice feature. I have some question though:

1. Will the XML forward slash "/" path separator be valid as well as the JSON dot "." notation? That way you could just change "FOR XML PATH" to "FOR JSON PATH" in your old XML queries without schema to migrate them to JSON. Ok it's very specific but it could be useful no?
2. How can JSON properties with name that includes a "." be managed? Will there be a way to escape them?

• Anonymous
  July 10, 2015
  The comment has been removed

• Anonymous
  October 14, 2015
  How about JSON to SQL table flatfile format?

• Anonymous
  October 15, 2015
  This will be available in CTP3 see blogs.msdn.com/.../openjson-the-easiest-way-to-import-json-text-into-table.aspx, and other JSON posts in this blog Jovan

• Anonymous
  December 13, 2015
  Hi Jovan I am really pleased to see support for JSON in SQL 2016 and that it seems simple to use.  One initial comment... It appears that FOR JSON PATH always outputs tailing zeros to the right of the decimal point up to whatever scale has been specified in the source data type.  For example, a numeric(38,20) containing a value of 5.04 will appear in the JSON document as 5.04000000000000000000. Is there a way of suppressing the trailing zeros without casting the datatype to something else.   Clearly the trailing zeros makes the JSON document less compact than it could be.  Neither casting the datatype prior to applying the FOR JSON PATH nor using the compress function afterwards are desirable in the scenario I'm thinking about. Thanks Justin

• Anonymous
  December 13, 2015
  Thanks Justin, I'm glad that you find it useful. I know that it looks strange but there is no way to change it in FOR JSON. If you just select numeric value or format it as FOR XML you will also get the trailing zeros: declare @num numeric(38,20) = 5.04 select @num select @num for xml path So, this is general behavior of SQL Server numeric-to-string conversion and we didn't want to add misalignment. The only way to avoid this is to explicitly cast numeric value: declare @num numeric(38,20) = 5.04 select cast(@num as numeric(5,2)) for xml path Regards, Jovan

• Anonymous
  December 14, 2015
  Hi Jovan I appreciate your prompt reply. It's a shame that there is no option to suppress the trailing zeros but I understand the reasons you give for not so doing. The problem with casting to another numeric type with a different precision and scale is that the precision and scale to use will depend upon the values and the values may differ dramatically for each row.  What I'm planning to do instead is to use the format function: declare @num numeric(38,20) = 5.04 select  format(@num, 'g38') as NumericValue for json path set     @num = 5.0400000000001 select  format(@num, 'g38') as NumericValue for json path JSON_F52E2B61-18A1-11d1-B105-00805F49916B

---

{"NumericValue":"5.04"} (1 row(s) affected) JSON_F52E2B61-18A1-11d1-B105-00805F49916B

{"NumericValue":"5.0400000000001"} (1 row(s) affected) Thanks Justin

• Anonymous
  December 28, 2015
  There is native Array type in SQL server 2016 ???