Category: Uncategorized

WITHOUT_ARRAY_WRAPPER

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
)
Only a single row is returned if the JSON is not in an array format.

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
)
All rows are brought back when the JSON is an array

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