JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

Question

SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting to use WITHOUT_ARRAY_WRAPPER to get rid of this. When used in a subquery odd results get returned...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc] 
 FROM sys.databases
 WHERE name in ('master', 'model', 'msdb')
 FOR JSON PATH
 ) AS d
FOR JSON PATH, ROOT('ServerInformation')

This produces expected data, with the array wrapper...

{"ServerInformation":[{"Servername":"MyServer","d":[{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}]}]}

However, WITHOUT_ARRAY_WRAPPER produces...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc] 
 FROM sys.databases
 WHERE name in ('master', 'model', 'msdb')
 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
 ) AS d
FOR JSON PATH, ROOT('ServerInformation')

{"ServerInformation":[{"Servername":"MyServer","d":"{\"Name\":\"master\",\"Recovery_Model_Desc\":\"SIMPLE\"},{\"Name\":\"model\",\"Recovery_Model_Desc\":\"FULL\"},{\"Name\":\"msdb\",\"Recovery_Model_Desc\":\"SIMPLE\"}"}]}

whereas I would expect it to produce

{"ServerInformation":{"Servername":"MyServer","d":{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}}}

Bug or expected results?

Edit: adjust expected results

• Mikael Eriksson: It is not entirely clear what result you are looking for. The one where you use WITHOUT_ARRAY_WRAPPER gives you the database list as a string value where " are escaped to \" as they must be. Your expected result is not valid JSON.

Answer 1

There's a blog post when the feature was introduced which says:

You would need to add WITHOUT_ARRAY_WRAPPER in your script if you want a single object, [...] WITHOUT_ARRAY_WRAPPER will not generate valid JSON text, [...] will treat NestedJson as any other plain text escape it and surround it with double quotes.

The post itself is a little confused and broken but my reading of this is that it's only intended to be used for a single row result and so nested JSON will be treated as a string.

Answer 2

use json_query,

SELECT @@SERVERNAME AS [Servername],
json_query(( SELECT [Name], [Recovery_Model_Desc] 
 FROM sys.databases
 WHERE name in ('master', 'model', 'msdb')
 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
 ) ) AS d
FOR JSON PATH, ROOT('ServerInformation')

• ttugates: Why are the double parenthesis needed after json_query and before AS d? Adding them fixed my own syntax issue but their need isn't clear to me.