WITHOUT_ARRAY_WRAPPER - New important change in FOR JSON

  • Article

In SQL Server 2016 CTP3.2 is added new option in FOR JSON clause - WITHOUT_ARRAY_WRAPPER see https://msdn.microsoft.com/en-us/library/mt631354.aspx

This option enables you to remove square brackets [ and ] that surround JSON text generated by FOR JSON clause. I will use the following example:

 SELECT 2015 as year, 12 as month, 15 as day
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

This query will return:

 { "year":2015, "month":12, "day":15 }

However, without this option, following text would be returned:

 [{ "year":2015, "month":12, "day":15 }]

This might be useful if you know that you are returning single row in the result set (e.g. if you return row by primary key, or use TOP 1), and you don't want array with one element in output.

Also, it might be useful if you want to concatenate two result sets from two queries formatted as JSON. In the earlier version you would need to remove wrapper, concatenate two outputs and wrap them with brackets to create valid JSON array:

set @merged = '[' + dbo_fnUnwrap( (SELECT .... FOR JSON PATH ) ) + ',' + dbo_fnUnwrap( (SELECT .... FOR JSON PATH ) )  + ']'

Now you don't need additional function that removes brackets:

set @merged = '[' + (SELECT .... FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )  + ',' + (SELECT .... FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )   + ']'

Why it is important? FOR JSON functionality is changed compared to CTP3.1 version. In previous versions there were some problems with FOR JSON PATH:

  1. Before CTP3.2, FOR JSON PATH clause that is applied on simple queries that didn't used table source (like in the example above where we don't have FROM clause) generated simple JSON object without [ ] wrapper. This was slightly confusing because some people are not aware what is behavior in this edge case, and assumed that it is a bug. Also, someone might assume that single object should be returned if query selects data by primary key or if TOP 1 is used, which makes sense in some cases. In order avoid confusion, FOR JSON PATH ALWAYS wraps results with square brackets. If you need to create single object you can remove square brackets using this option.
  2. There was some bugs in generated JSON with FOR JSON PATH. If you applied FOR JSON PATH on CTE, or TVF you might get JSON array without square brackets. In CTP3.2 brackets are always generated so output is always valid.

Therefore, if you relied on the fact that FOR JSON will sometime return a single JSON object beware that this behavior is changed. You would need to add WITHOUT_ARRAY_WRAPPER in your script if you want a single object.

Note one important thing - WITHOUT_ARRAY_WRAPPER will not generate valid JSON text. If you try to put FOR JSON WITHOUT_ARRAY_WRAPPER as column expression, it will be treated as a plain text, e.g.:

SELECT col1, col2, col3, (SELECT .... FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) AS NestedJson
FROM mytab
FOR JSON PATH

FOR JSON path will treat NestedJson as any other plain text escape it and surround it with double quotes.