5

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

  • 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. – Mikael Eriksson Aug 31 '16 at 7:55
3

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.

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.