When working on system integrations, JSON is often the format of choice used to exchange data. As with any data contract, the clarity and structure of the schema matter. Default behaviors (like wrapping JSON into arrays) can complicate things when you’re expecting a single object. This is especially true when processing feeds or doing asynchronous operations.

I dabble a lot in data operations and often use SQL Server queries to build my JSON payloads. As we know, we can use the FOR JSON PATH clause to generate the output as a JSON.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] BETWEEN 1600 AND 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	},
	{
		"FirstName": "David",
		"LastName": "Robinett",
		"PhoneNumber": "238-555-0100",
		"City": "Solingen"
	}
]

If you observe carefully, the output is a JSON array. This is true even if one (1) object is being returned – which then requires me to edit the payload manually. Here’s the example:

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	}
]

Introducing WITHOUT_ARRAY_WRAPPER

Starting SQL Server 2016, a new option (WITHOUT_ARRAY_WRAPPER) is now available. This option can be used with a single-row result to generate a single JSON object as output instead of an array with a single element.

Here’s the same example as above, but with the WITHOUT_ARRAY_WRAPPER clause applied.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
GO

{
	"FirstName": "Rebecca",
	"LastName": "Robinson",
	"PhoneNumber": "648-555-0100",
	"City": "Seaford"
}

Practical use-cases

Below are some practical use-cases where the WITHOUT_ARRAY_WRAPPER option has been very handy for me:

1. Creating Samples – for API requests, responses and writing documentation!
2. Nesting/embedding JSON in another JSON
3. Logging or auditing single events

Word of caution

If you use this option with a multiple-row result, no error will be returned. However, the resulting output is not valid JSON because of the multiple elements and the missing square brackets.

Call to Action

I trust you found this quick nugget helpful. Do let me know if you already knew about it before. I would be interested to read about the various cases in which you have used the FOR JSON PATH, WITHOUT_ARRARY_WRAPPER option.

Until we meet next time,

Be courteous. Drive responsibly.

Disclaimer: The [AdventureWorks2022] sample database (https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver17&tabs=ssms) has been used for all test data used in this post.