JSON functions and examples in SQL Server

In this blog, we will see the use of JSON functions and examples in SQL Server. We will also see how to query and modify JSON data using SQL Server Query.
As you already know JSON stands for JavaScript Object Notation, which is a lightweight computer data interchange format.
We can use JSON data in SQL Server tables as well. There is no separate data type for JSON available in SQL Server.
You can store JSON data in the NVARCHAR type.

JSON Functions

So let’s explore JSON functions given by SQL Server.

1. ISJSON –  

This JSON function checks if the given data is a valid JSON or not. It returns 1 if the string is a valid JSON else returns 0. This method does not return errors.

Syntax – ISJSON (string_value)

select ISJSON('{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}')

Example1 – Passed a valid JSON string

Output – 1

Example1 – Passed invalid JSON string

select ISJSON('{name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}')

Output – 0

2. JSON_VALUE – 

This function is used to extract scalar values from JSON string, but before getting a value it checks whether the JSON string is valid or not. If the given JSON string is not valid, it returns an error.

Syntax – JSON_VALUE(json_ string, value_to_extract)

Example 1 –

declare @emp_json_data nvarchar(max)    = '{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}'

Find name from the above JSON string

Query –

declare @name nvarchar(100) 
 
set @name = JSON_VALUE(@emp_json_data, '$.name') 
 
select @name as [Employee Name]

Output – Ram

Example 2 – Get the name, If there is a list of employees or an array in JSON string.

declare @emp_json_data nvarchar(max)    = '[{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}, {"name":   "Laxman","age": "38","empid":   "T115","dept": "D2","location":   "Delhi"}]'

In the above JSON string, there are 2 records that are in form of an array.

Query –

declare @name nvarchar(100) 
 
set @name = JSON_VALUE(@emp_json_data, '$[1].name') 
 
select @name as [Employee Name]

Output – Laxman

Example 3 –

declare @emp_json_data nvarchar(max)    = '{"Employee":[{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}, {"name": "Laxman","age":   "38","empid": "T115","dept":   "D2","location": "Delhi"}]}'

Query –

declare @name nvarchar(100) 
 
set @name = JSON_VALUE(@emp_json_data, '$.Employee[1].name') 
 
select @name as [Employee Name]

Output – Laxman

3. JSON_QUERY –

This JSON function in SQL Server extracts an object or an array from a valid JSON string.

Syntax – JSON_QUERY(json_string, [, path])

Example 1  – Select first record from below JSON string

declare @emp_json_data nvarchar(max)    = '{"Employee":[{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}, {"name": "Laxman","age":   "38","empid": "T115","dept":   "D2","location": "Delhi"}]}'

Query –

declare @name nvarchar(100) 
 
select JSON_QUERY(@emp_json_data, '$.Employee[0]')

Output – {“name”: “Ram”,”age”: “40”,”empid”: “T112″,”dept”: “D1″,”location”: “Bangalore”}

4. JSON_MODIFY – 

This function updates the value of the given JSON string and then returns the new JSON string.

Syntax –

JSON_MODIFY(expression, path, new value)

Example 1 – Update the dept of the first record in the below JSON string.

declare @emp_json_data nvarchar(max)    = '{"Employee":[{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}, {"name":   "Laxman","age": "38","empid":   "T115","dept": "D2","location":   "Delhi"}]}'

Query –

declare @newJsonWithDept nvarchar(max) 
 
set @newJsonWithDept = JSON_MODIFY(@emp_json_data,'$.Employee[0].dept', 'D3') 
 
select @newJsonWithDept

Output – {“Employee”:[{“name”: “Ram”,”age”: “40”,”empid”: “T112″,”dept”: “D3″,”location”: “Bangalore”}, {“name”: “Laxman”,”age”: “38”,”empid”: “T115″,”dept”: “D2″,”location”: “Delhi”}]}

5. OPENJSON – 

OPENJSON function introduced in SQL Server 2016. This function is used to convert JSON data into one or multiple rows.

Syntax – OPENJSON(json_expression)

Example – Select all values from the below JSON string.

JSON String –

declare @emp_json_data nvarchar(max)    = '{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}'

Query –

select * from OPENJSON(@emp_json_data)

Output –

Example 2 –

JSON String –

declare @emp_json_data nvarchar(max)    = '{"Employee":[{"name":   "Ram","age": "40","empid":   "T112","dept": "D1","location":   "Bangalore"}, {"name": "Laxman","age":   "38","empid": "T115","dept":   "D2","location": "Delhi"}]}'

Query –

select * from OPENJSON(@emp_json_data) with ([Employee Name] nvarchar(100) '$.Employee[0].name',Age nvarchar(100) '$.Employee[0].age', 
EmpId nvarchar(100) '$.Employee[0].empid',Department nvarchar(100) '$.Employee[0].dept',City nvarchar(100) '$.Employee[0].location')

Output –

For Practice, I have shared a sample JSON file below. You may download and write queries accordingly.

Download the JSON_Sample file and now let’s play around with the JSON structure.

declare @json nvarchar(max)
set @json = 'File Content Downloaded'

Q. Query to get a list of Country and Continent from JSON string.

Query –

select * from openjson(@json)
with
(
name nvarchar(100),
continent nvarchar(100)
)

Q. Write a query to get output in the below format from the downloaded JSON string.

Query –

select JSON1.name as Country, JSON2.City from openjson(@json)
with
(
name nvarchar(100),
citylist nvarchar(max) as json
) as JSON1
cross apply openjson(JSON1.citylist)
with
(
City nvarchar(100)
) as JSON2

Q. Write a query to get the list of cities of India from JSON string

Query –

select JSON1.name as Country, JSON2.City from openjson(@json)
with
(
name nvarchar(100),
citylist nvarchar(max) as json
) as JSON1
cross apply openjson(JSON1.citylist)
with
(
City nvarchar(100)
) as JSON2
where JSON1.name = 'India'

Q. Write a query to show the result where the city is Bangalore from JSON string

Query –

select JSON1.name as Country, JSON2.City from openjson(@json)
with
(
name nvarchar(100),
citylist nvarchar(max) as json
) as JSON1
cross apply openjson(JSON1.citylist)
with
(
City nvarchar(100)
) as JSON2
where JSON2.City = 'Bangalore'

Hope you like this blog. Keep following this blog.

You may like other blogs –

• MVC Tutorial
• Web API Tutorial
• Angular Tutorial
• Learn TypeScript

Interview Questions and Answers Series –

MVC Interview Questions and Answers
Web API interview questions and answers

Keep following – SharePointCafe.Net