JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?
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')
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.