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:
This query will return:
However, without this option, following text would be returned:
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.