remove-square-brackets-from-json-without-array-wrapper-option.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.topic	helpviewer_keywords	monikerRange
Remove Square Brackets from JSON - WITHOUT_ARRAY_WRAPPER Option	Remove Square Brackets from JSON - WITHOUT_ARRAY_WRAPPER Option	jovanpop-msft	jovanpop	jroth	06/03/2020	sql	conceptual	WITHOUT_ARRAY_WRAPPER	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Remove Square Brackets from JSON - WITHOUT_ARRAY_WRAPPER Option

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-serverless-pool-only]

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
}]

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft videos

Note

Some of the video links in this section may not work at this time. Microsoft is migrating content formerly on Channel 9 to a new platform. We will update the links as the videos are migrated to the new platform.

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

• JSON as a bridge between NoSQL and relational worlds

See Also

FOR Clause (Transact-SQL)