JSON (JavaScript Object Notation) is a popular data format used for storing and exchanging data. In SQL Server, we can work with JSON data using the OPENJSON function. OPENJSON allows us to parse and query JSON data in various ways.

There are two ways to use OPENJSON: with an explicit schema or with the default schema. In this article, we will focus on using OPENJSON with the default schema.

Let’s start by looking at an example of using OPENJSON with the default schema:

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
  },
  {
    "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
  }
]'
);

In this example, we have a JSON array with three objects. By using OPENJSON, we can retrieve the values and types of the properties in each object. The “type” column indicates the data type of each property.

We can also work with more complex JSON structures. Here’s an example:

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
  }
}'
);

In this case, we have a JSON object with nested objects. OPENJSON allows us to access the properties at different levels and retrieve their values and types.

To make it easier to work with the JSON data, we can insert the values into a table. Here’s an example:

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,
  [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;

In this example, we create a table called “JSONDetails” and insert the values and types of the properties into the table using OPENJSON. This allows us to easily retrieve the data later.

Using OPENJSON with the default schema provides a flexible way to work with JSON data in SQL Server. It allows us to parse and query JSON data, retrieve property values and types, and even insert the data into tables for further analysis.

Stay tuned for the next installment where we will explore using OPENJSON with an explicit schema. Happy querying!

Cheers,

Marty