patternsqlMinor
JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?
Viewed 0 times
without_array_wrapperreturningsubqueryusingjsondataodd
Problem
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...
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...
{"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
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
Solution
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')Code Snippets
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')Context
StackExchange Database Administrators Q#148383, answer score: 7
Revisions (0)
No revisions yet.