Remove Square Brackets from JSON with the WITHOUT_ARRAY_WRAPPER Option#
To remove the square brackets that surround the JSON output of the FOR JSON clause by default, specify the WITHOUT_ARRAY_WRAPPER option. Use this option with a single-row result to generate a single JSON object as output instead of an array with a single element.
If you use this option with a multiple-row result, the resulting output is not valid JSON because of the multiple elements and the missing square brackets.
Example (single-row result)#
The following example shows the output of the FOR JSON clause with and without the WITHOUT_ARRAY_WRAPPER option.
Query
SELECT 2015 as year, 12 as month, 15 as day
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Result with the WITHOUT_ARRAY_WRAPPER option
{
"year": 2015,
"month": 12,
"day": 15
}
Result (default) without the WITHOUT_ARRAY_WRAPPER option
[{
"year": 2015,
"month": 12,
"day": 15
}]
Example (multiple-row result)
Here’s another example of a FOR JSON clause with and without the WITHOUT_ARRAY_WRAPPER option. This example produces a multiple-row result. The output is not valid JSON because of the multiple elements and the missing square brackets.
Query
SELECT TOP 3 SalesOrderNumber, OrderDate, Status
FROM Sales.SalesOrderHeader
ORDER BY ModifiedDate
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Result with the WITHOUT_ARRAY_WRAPPER option
{
"SalesOrderNumber": "SO43662",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}, {
"SalesOrderNumber": "SO43661",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}, {
"SalesOrderNumber": "SO43660",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}
Result (default) without the WITHOUT_ARRAY_WRAPPER option
[{
"SalesOrderNumber": "SO43662",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}, {
"SalesOrderNumber": "SO43661",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}, {
"SalesOrderNumber": "SO43660",
"OrderDate": "2011-05-31T00:00:00",
"Status": 5
}]