Introduction to OPENJSON with Examples (SQL Server)

SQL Server has a table-valued function called OPENJSON() that creates a relational view of JSON data.

When you call it, you pass a JSON document as an argument, and OPENJSON() then parses it and returns the JSON document’s objects and properties in a tabular format – as rows and columns.

Example

Here’s a simple example to demonstrate.

SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

By default, OPENJSON() returns a table with three columns; key, value, and type.

You also have the option of specifying your own schema (which means you can define your own columns). In my simple example, I used the default schema, and so the three default columns were returned.

These columns are defined as follows:

Column	Description
key	Contains the name of the specified property or the index of the element in the specified array. This is an nvarchar(4000) value, and the column has a BIN2 collation.
value	Contains the value of the property. This is an nvarchar(max) value, and the column inherits its collation from the JSON provided.
type	Contains the JSON type of the value. This is represented as an int value (from `0` to `5`). This column is only returned when you use the default schema.

Default Types

In the world of JSON, there are six data types. These are string, number, true/false (boolean), null, object, and array.

When you parse some JSON through OPENJSON() using the default schema, OPENJSON() works out what the JSON type is, and then populates the type column with an int value that represents that type.

The int value can therefore range from 0 to 5. Each int value represents a JSON type as outlined in the following table.

Value in the “type” column	JSON data type
0	null
1	string
2	number
3	true/false
4	array
5	object

The following example returns all six of these JSON types.

SELECT * FROM OPENJSON('{"name" : null}');
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');
SELECT * FROM OPENJSON('[1,2,3]');
SELECT * FROM OPENJSON('[true,false]');
SELECT * FROM OPENJSON('{"cats":[{ "id":1, "name":"Fluffy"},{ "id":2, "name":"Scratch"}]}');
SELECT * FROM OPENJSON('[{"A":1,"B":0,"C":1}]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| name  | NULL    | 0      |
+-------+---------+--------+
(1 row affected)
+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+
(3 rows affected)
+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | 1       | 2      |
| 1     | 2       | 2      |
| 2     | 3       | 2      |
+-------+---------+--------+
(3 rows affected)
+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | true    | 3      |
| 1     | false   | 3      |
+-------+---------+--------+
(2 rows affected)
+-------+----------------------------------------------------------+--------+
| key   | value                                                    | type   |
|-------+----------------------------------------------------------+--------|
| cats  | [{ "id":1, "name":"Fluffy"},{ "id":2, "name":"Scratch"}] | 4      |
+-------+----------------------------------------------------------+--------+
(1 row affected)
+-------+---------------------+--------+
| key   | value               | type   |
|-------+---------------------+--------|
| 0     | {"A":1,"B":0,"C":1} | 5      |
+-------+---------------------+--------+
(1 row affected)

Return Nested JSON

You can return a nested object or array by specifying its path as an optional second argument.

In other words, you don’t have to parse the whole JSON document – you can choose to parse just the part that you’re interested in.

Here’s an example.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, '$.pets.cats');

Result:

+-------+------------------------------------------------------+--------+
| key   | value                                                | type   |
|-------+------------------------------------------------------+--------|
| 0     | { "id" : 1, "name" : "Fluffy", "sex" : "Female" }    | 5      |
| 1     | { "id" : 2, "name" : "Long Tail", "sex" : "Female" } | 5      |
| 2     | { "id" : 3, "name" : "Scratch", "sex" : "Male" }     | 5      |
+-------+------------------------------------------------------+--------+

In this case, I specified a path of $.pets.cats, which resulted in only the value of cats being returned. The value of cats is an array, so the whole array was returned.

To return just one cat (i.e. one array element), we can use the square bracket syntax for returning array values (like this $.pets.cats[1]).

Here’s the same example modified to return just one array element:

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, '$.pets.cats[1]');

Result:

+-------+-----------+--------+
| key   | value     | type   |
|-------+-----------+--------|
| id    | 2         | 2      |
| name  | Long Tail | 1      |
| sex   | Female    | 1      |
+-------+-----------+--------+

JSON array indexes are zero-based, so this example returned the second array value (because I specified $.pets.cats[1]).

If I had specified $.pets.cats[0], the first value would have been returned (i.e. the cat named “Fluffy”).

Define a Schema

As mentioned, you can specify your own schema (i.e. define your own columns and types).

Here’s an example of doing that.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT * FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Sex]       varchar(6)      '$.sex', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );

Result:

+----------+------------+--------+------------------------------------------------------+
| Cat Id   | Cat Name   | Sex    | Cats                                                 |
|----------+------------+--------+------------------------------------------------------|
| 1        | Fluffy     | Female | { "id" : 1, "name" : "Fluffy", "sex" : "Female" }    |
| 2        | Long Tail  | Female | { "id" : 2, "name" : "Long Tail", "sex" : "Female" } |
| 3        | Scratch    | Male   | { "id" : 3, "name" : "Scratch", "sex" : "Male" }     |
+----------+------------+--------+------------------------------------------------------+

We can see that the column names reflect those that I specified in the WITH clause. In that clause, I mapped each JSON key to my own preferred column names. I also specified the SQL Server data type that I want for each column.

I also used AS JSON on the last column to return that column as a JSON fragment. When you use AS JSON, the data type must be nvarchar(max).

Verify the Data Types

We can use the following query to verify the data types of each column.

This query uses the sys.dm_exec_describe_first_result_set system dynamic management view, which returns metadata about the first result set from a query.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT 
    name,
    system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT * FROM OPENJSON(@json, ''$.pets.cats'') WITH  (
        [Cat Id]    int             ''$.id'',  
        [Cat Name]  varchar(60)     ''$.name'', 
        [Sex]       varchar(6)      ''$.sex'', 
        [Cats]      nvarchar(max)   ''$'' AS JSON 
    )',
    null,
    0
);

Result:

+----------+--------------------+
| name     | system_type_name   |
|----------+--------------------|
| Cat Id   | int                |
| Cat Name | varchar(60)        |
| Sex      | varchar(6)         |
| Cats     | nvarchar(max)      |
+----------+--------------------+

We can see that they match up perfectly my schema.

Note that the key, value, and type columns are not available when you define your own schema. Those columns are only available when using the default schema.

Insert the Parsed JSON into a Table

By now you might be thinking that we could easily insert our parsed JSON into a database table.

And you’d be right.

We’ve already prepared it with columns and rows, and we’ve even named the columns and given them data types.

Now it’s time to insert it into a table.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT * INTO JsonCats
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Sex]       varchar(6)      '$.sex', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );

All I did was add INTO JsonCats to my query, in order to create a table called JsonCats and insert the results of the query into it.

Now let’s select the contents of that table.

SELECT * FROM JsonCats;

Result:

+----------+------------+--------+------------------------------------------------------+
| Cat Id   | Cat Name   | Sex    | Cats                                                 |
|----------+------------+--------+------------------------------------------------------|
| 1        | Fluffy     | Female | { "id" : 1, "name" : "Fluffy", "sex" : "Female" }    |
| 2        | Long Tail  | Female | { "id" : 2, "name" : "Long Tail", "sex" : "Female" } |
| 3        | Scratch    | Male   | { "id" : 3, "name" : "Scratch", "sex" : "Male" }     |
+----------+------------+--------+------------------------------------------------------+

The contents are exactly as we saw them in the earlier example.

And just to be absolutely sure, we can now use the sys.column system catalog view check the table’s column names and types.

SELECT
    name AS [Column],
    TYPE_NAME(system_type_id) AS [Type],
    max_length
FROM sys.columns 
WHERE OBJECT_ID('JsonCats') = object_id;

Result:

+----------+----------+--------------+
| Column   | Type     | max_length   |
|----------+----------+--------------|
| Cat Id   | int      | 4            |
| Cat Name | varchar  | 60           |
| Sex      | varchar  | 6            |
| Cats     | nvarchar | -1           |
+----------+----------+--------------+

Again, exactly how we’d specified it.

Note that sys.columns always returns a max_length of -1 when the column data type is varchar(max), nvarchar(max), varbinary(max), or xml. We specified nvarchar(max) and so the value of -1 is exactly as expected.

Path Mode: Lax vs Strict

The path provided in the second argument or in the WITH clause can (optionally) start with the lax or strict keyword.

In lax mode, OPENJSON() doesn’t raise an error if the object or value on the specified path can’t be found. If the path can’t be found, OPENJSON() returns either an empty result set or a NULL value.
In strict mode, OPENJSON() returns an error if the path can’t be found.

The default value is lax, so if you don’t specify a path mode, lax mode will be used.

Here are some examples to demonstrate what happens with each mode when the path cannot be found.

Second Argument

In the next two examples, I provide a non-existent path in the second argument when calling OPENJSON(). The first example shows what happens when using lax mode, the second example shows what happens when using strict mode.

Lax Mode

Here’s what happens in lax mode when the path can’t be found.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';
SELECT * FROM OPENJSON(@json, 'lax $.pets.cows');

Result:

(0 rows affected)

No error. Just zero results returned.

Strict Mode

Now here it is in strict mode.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}'
SELECT * FROM OPENJSON(@json, 'strict $.pets.cows');

Result:

Msg 13608, Level 16, State 3, Line 15
Property cannot be found on the specified JSON path.

As expected, strict mode resulted in an error.

In the WITH Clause

In the next two examples, we again test lax mode vs strict mode, except this time we specify it in the WITH clause when defining the schema.

Lax Mode

Here’s what happens in lax mode.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT *
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Born]      date            'lax $.born', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );

Result:

+----------+------------+--------+------------------------------------------------------+
| Cat Id   | Cat Name   | Born   | Cats                                                 |
|----------+------------+--------+------------------------------------------------------|
| 1        | Fluffy     | NULL   | { "id" : 1, "name" : "Fluffy", "sex" : "Female" }    |
| 2        | Long Tail  | NULL   | { "id" : 2, "name" : "Long Tail", "sex" : "Female" } |
| 3        | Scratch    | NULL   | { "id" : 3, "name" : "Scratch", "sex" : "Male" }     |
+----------+------------+--------+------------------------------------------------------+

In this case I use 'lax $.born' because I’m trying to reference a key called born, but such a key doesn’t exist in the JSON.

This time the column that can’t be found results in a NULL value.

Strict Mode

Now here it is in strict mode.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

SELECT *
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        [Cat Id]    int             '$.id',  
        [Cat Name]  varchar(60)     '$.name', 
        [Born]      date            'strict $.born', 
        [Cats]      nvarchar(max)   '$' AS JSON   
    );

Result:

Msg 13608, Level 16, State 6, Line 16
Property cannot be found on the specified JSON path.

This time I used 'strict $.born'.

As expected, strict mode resulted in an error.

Compatibility Level

The OPENJSON() function is available only under compatibility level 130 or higher.

If your database compatibility level is lower than 130, SQL Server won’t be able to find and run OPENJSON(), and you’ll get an error.

You can check your database compatibility level via the sys.databases catalog view.

You can change its compatibility level like this:

ALTER DATABASE DatabaseName 
SET COMPATIBILITY_LEVEL = 150;

New to JSON?

If you’re not so familiar with JSON, check out my JSON tutorial over at Quackit.