Using OPENJSON Function In SQL Server

JSON

JSON is a text format that is completely language-independent. Meaning it does not dependent on any specific programming language. It is a very lightweight data-interchange format. Moreover, very easy for humans to read and write.

OPENJSON

OpenJSON is a SQL function. That converts JSON text and returns a key and value in a row and column format. Meaning it helps to make JSON queryable.

Note

Microsoft suggests storing JSON text using the nvarchar(max) data type. This data type supports up to 2GB of storage per value. The OPENJSON function available under compatibility level 130.

OPENJSON has 2 arguments and one clause option. Like:

JSONExpression Argument

JSON Expression means JSON text. It is a required argument for this function. So they must be defined by the JSON Expression.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
    {
		"id":1,
        "team": "Bangladesh",
        "player": {
            "name": "Mashrafe Murtuza",
            "size": {
                "width": "74 kg",
                "hight": "6.1 feet"
            }
        },
        "opposite_team": null,
        "captain": ["Mashrafe Murtuza (C)", "Tamim Iqbal (VC)"],
        "good_position": true,
        "icc_associate_member": 1977
    }'
select * from OPENJSON(@json) order by type

Note

Remember that type is an auto-generated column according to JSON data type.

Path Argument

Meaning retrieving a specific object of JSON Expression/text. It is an optional argument. The path always begins with a dollar ($) sign. There are two modes for using Path. Like:

Lax Mode

It is the default mode. So, no need to explicitly define the Lax keyword. If the path does not exist, it will return a NULL or empty row and not raise any error in executing time if the path does not exist.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
    {
		"id":1,
        "team": "Bangladesh",
        "player": {
            "name": "Mashrafe Murtuza",
            "size": {
                "width": "74 kg",
                "hight": "6.1 feet"
            }
        },
        "opposite_team": null,
        "captain": ["Mashrafe Murtuza (C)", "Tamim Iqbal (VC)"],
        "good_position": true,
        "icc_associate_member": 1977
    }'
select * from OPENJSON(@json, '$.player.size') order by type

When not exists the explicit path in Lax mode.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
    {
		"id":1,
        "team": "Bangladesh",
        "player": {
            "name": "Mashrafe Murtuza",
            "size": {
                "width": "74 kg",
                "hight": "6.1 feet"
            }
        },
        "opposite_team": null,
        "captain": ["Mashrafe Murtuza (C)", "Tamim Iqbal (VC)"],
        "good_position": true,
        "icc_associate_member": 1977
    }'
select * from OPENJSON(@json, '$.player.type') order by type

Strict Mode

It will raise an error in executing time if an explicit path does not exist. We need to define the explicitly strict keyword with $ sign.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
    {
		"id":1,
        "team": "Bangladesh",
        "player": {
            "name": "Mashrafe Murtuza",
            "size": {
                "width": "74 kg",
                "hight": "6.1 feet"
            }
        },
        "opposite_team": null,
        "captain": ["Mashrafe Murtuza (C)", "Tamim Iqbal (VC)"],
        "good_position": true,
        "icc_associate_member": 1977
    }'
select * from OPENJSON(@json, 'strict$.player.type') order by type

With Clause

It is used to define outputted result set with clause map JSON values to the custom column in the result set.

DECLARE @json NVARCHAR(MAX)
SET @json = N'
    {
		"id":1,
        "team": "Bangladesh",
        "player": {
            "name": "Mashrafe Murtuza",
            "size": {
                "width": "74 kg",
                "hight": "6.1 feet"
            }
        },
        "opposite_team": null,
        "captain": ["Mashrafe Murtuza (C)", "Tamim Iqbal (VC)"],
        "good_position": true,
        "icc_associate_member": 1977
    }'
select * from OPENJSON(@json, '$.player.size')
with(
	Width varchar(50) '$."width"',
	Hight varchar(50) '$."hight"'
)

Some more use of the clause

Basically, with clause is used after the OPENJSON function. This clause makes the OPENJSON function very much more robust. We will discuss below some use for with clause.

Creating a result set from OPENJSON with specific SQL datatype

DECLARE @json NVARCHAR(MAX) = N'
[{
    "name": "Shakib Al Hasan",
    "stats": {"title":"All-rounder", "best": 134}
},
{
    "name": "Tamim Iqbal",
    "stats": {"title":"Batsman", "best": 158}
},
{
    "name": "Mushfiqur Rahim",
    "stats": {"title":"Wicketkeeper", "best": 144}
}]
'
select * from openjson(@json)
with 
(
	Name varchar(50) '$.name',
	Title varchar(50) '$.stats.title',
	Best int '$.stats.best'
)

Query JSON text from a DB table

Let’s assume that we have a DB table called player. This table contains some JSON text. Now we can make it queryable by using cross-apply operand and OPENJSON.

CREATE TABLE [dbo].[Player](
	[Id] [int] NOT NULL PRIMARY KEY,
	[Json] [nvarchar](max) NULL
)

INSERT [dbo].[Player] ([Id], [Json]) VALUES (1, N'[{
 "code":"BAN",   "name": "Tamim Iqbal",
    "title":"Batsman",
	"style": "Left-handed",
	"best": 158,
	"running" : 1
}]')
GO
INSERT [dbo].[Player] ([Id], [Json]) VALUES (2, N'[{
 "code":"BAN",   "name": "Mushfiqur Rahim",
    "title":"Wicketkeeper",
	"style": "Right-Handed",
	"best": 144,
	"running" : 1
}]')
GO
INSERT [dbo].[Player] ([Id], [Json]) VALUES (3, N'[{
	"code": "IND",
    "name": "Mohammad Kaif",
    "title":"Batsman",
	"style": "Right-handed",
	"best": 111,
	"running" : 0
}]')
GO
INSERT [dbo].[Player] ([Id], [Json]) VALUES (4, N'[{
	"code": "PAK",
    "name": "Mohammad Yousuf",
    "title":"Batsman",
	"style": "Right-handed",
	"best": 141,
	"running" : 0
}]')

Join with OPENJSON and Cross Apply Result set

CREATE TABLE [dbo].[Team](
	[Code] [varchar](3) NOT NULL PRIMARY KEY,
	[Country] [varchar](20) NULL)

INSERT [dbo].[Team] ([Code], [Country]) VALUES (N'BAN', N'Bangladesh')
GO
INSERT [dbo].[Team] ([Code], [Country]) VALUES (N'IND', N'India')
GO
INSERT [dbo].[Team] ([Code], [Country]) VALUES (N'PAK', N'Pakistan')
GO
INSERT [dbo].[Team] ([Code], [Country]) VALUES (N'SRI', N'Srilanka')

select result.*,Team.Country from 
Player
CROSS APPLY OPENJSON(Json)
with (
	Code varchar(3) '$.code',
	Name varchar(50) '$.name',
	Title varchar(50) '$.title',
	Style varchar(50) '$.style',
	Score int '$.best',
	Running bit '$.running'
) as result Inner join Team on result.Code = team.Code
order by result.Score

Summary

Knowing about the OPENJSON function of SQL Server will be helpful: happy coding, and thanks for reading my article.