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.