HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

Submitted by: @import:stackexchange-dba··
0
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...

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.