OPENJSON – using the default schema to collect new information

We’ve looked at reading JSON from disk and also verifying that a string we have contains valid JSON data. But, naturally, we’d like to do more than that.

Well, there is a method of doing just that and that is using the OPENJSON function.

We can use OPENJSON in two forms – either when we know the schema of the JSON document (or at least the part of the schema that we want to retreive). This is know as using OPENJSON with an explicit schema – we’ll examine this in a latter installment.

The other way of using OPENJSON (and the one we’ll examine in this section) is known as using OPENJSON with the default schema.

SELECT * FROM 
OPENJSON
(
'
[
  {
    "configuration_id": 101,
    "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,
    "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
  },
  {
    "configuration_id": 103,
    "name": "user connections",
    "value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Number of user connections allowed",
    "is_dynamic": false,
    "is_advanced": true
  }
]
'
);

Let’s look

And we get the flowing results.

Notice that we had three arrays in our JSON document and we have three lines in our resultset.

Notice also that the “type” column has a value of 5.

Let’s have a look at another piece of JSON – one that might be a bit more interesting

SELECT 
 
,[value]
,[type]
FROM 
OPENJSON
(
'
{
"configuration_id": 101,
"Configuration_Property": {
"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 time we get the output

Notice now that we get a new row and a new number in the type column. Maybe each different type has it’s own number. Let’s find something even more interesting.

SELECT 
 
,[value]
,[type]
FROM 
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
);

And this time the output looks like

So, the numbers in the “type” column are indeed very relevant. There exact meaning is listed in the docs, but here’s a brief summary.

We can always insert the values into a table for easy retrieval later.

IF OBJECT_ID('dbo.JSONDetails') IS NOT NULL
BEGIN
DROP TABLE dbo.JSONDetails;
END;
CREATE TABLE dbo.JSONDetails
(
 JSONDetailID INT IDENTITY(1,1) NOT NULL
,        NVARCHAR(4000)
,[value]      NVARCHAR(MAX) NULL
,[type]       INT NOT NULL
,[type_desc] AS
(
CASE
WHEN [type] = 0 THEN 'Null'
WHEN [type] = 1 THEN 'String'
WHEN [type] = 2 THEN 'Number'
WHEN [type] = 3 THEN 'Boolean'
WHEN [type] = 4 THEN 'Array'
WHEN [type] = 5 THEN 'Object'
ELSE 'ERROR'
END
)
)
INSERT INTO dbo.JSONDetails
(
  
,[value]
,[type]
)
SELECT 
 
,[value]
,[type]
FROM 
OPENJSON
(
'
{
"configuration_id": 101,
"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
}
'
);
SELECT * FROM dbo.JSONDetails AS J ORDER BY JSONDetailID;

For a full description of the data types of key, value and type using OPENJSON, just see the docs.

Next up is using OPENJSON with an explicit schema.

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

OPENJSON – using the default schema and collect new information