When we look at a single JSON object we can view it as
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
But when we have multiple objects , we can see them presented as
[
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
},
{
"configuration_id": 102,
"Configuration name": "allow updates",
"Value": 0,
"minimum": 0,
"maximum": 1,
"value_in_use": 0,
"description": "Allow updates to system tables",
"is_dynamic": true,
"is_advanced": false
}
]
However, say we just wanted to get a single row from an SQL Server table and present the JSON as in the first example above
We might try something of the form (note that we are only explicitly asking for 1 row, we could do that with a WHERE clause also – rather than a TOP 1)
SELECT
TOP 1
[configuration_id] = C.configuration_id
,[Configuration name] = C.[name]
,[Value] = C.[value]
,[minimum] = C.minimum
,[maximum] = C.maximum
,[value_in_use] = C.value_in_use
,[description] = C.[description]
,[is_dynamic] = C.is_dynamic
,[is_advanced] = C.is_advanced
FROM
sys.configurations AS C
ORDER BY
C.configuration_id
FOR JSON PATH
But the JSON we get back is one element in an array
[
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
]
This, may not be what we want. We may for instance want to combine JSON objects and will see how to do that at some stage.
But what if we wanted this single objects brought back on it’s own and not in an array?
Well, we just add the clause WITHOUT_ARRAY_WRAPPER to either JSON AUTO or JSON PATH
SELECT
TOP 1
[configuration_id] = C.configuration_id
,[Configuration name] = C.[name]
,[Value] = C.[value]
,[minimum] = C.minimum
,[maximum] = C.maximum
,[value_in_use] = C.value_in_use
,[description] = C.[description]
,[is_dynamic] = C.is_dynamic
,[is_advanced] = C.is_advanced
FROM
sys.configurations AS C
ORDER BY
C.configuration_id
FOR
JSON PATH ,
WITHOUT_ARRAY_WRAPPER
This can be an amazingly useful function if we want to join different JSON documents together.
The OPENJSON function will still read a document without the array wrapper – for instance.
SELECT
*
FROM
OPENJSON
(
'
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
},
{
"configuration_id": 102,
"Configuration name": "allow updates",
"Value": 0,
"minimum": 0,
"maximum": 1,
"value_in_use": 0,
"description": "Allow updates to system tables",
"is_dynamic": true,
"is_advanced": false
}
',
'$'
)
WITH
(
[configuration_id] INT
,[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(200)
,[minimum] NVARCHAR(200)
,[maximum] NVARCHAR(200)
,[value_in_use] NVARCHAR(200)
,[description] NVARCHAR(200)
,[is_dynamic] BIT
,[is_advanced] BIT
)

Note however that only data from the first element is returned in the tabular resultset
If we want both rows (only 2 in this case)
Then the elements will need to be wrapped as an array – like so
SELECT
*
FROM
OPENJSON
(
'
[
{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
},
{
"configuration_id": 102,
"Configuration name": "allow updates",
"Value": 0,
"minimum": 0,
"maximum": 1,
"value_in_use": 0,
"description": "Allow updates to system tables",
"is_dynamic": true,
"is_advanced": false
}
]
',
'$'
)
WITH
(
[configuration_id] INT
,[Configuration name] NVARCHAR(35)
,[Value] NVARCHAR(200)
,[minimum] NVARCHAR(200)
,[maximum] NVARCHAR(200)
,[value_in_use] NVARCHAR(200)
,[description] NVARCHAR(200)
,[is_dynamic] BIT
,[is_advanced] BIT
)

You can read more about the WITHOUT_ARRAY_WRAPPER function in the docs.
I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.
Have a great day
Cheers
Marty
Download Files
WITHOUT_ARRAY_WRAPPER