JSON functions and examples in SQL Server

In this blog, we will see the use of JSON functions in SQL Server. We will also see how to query and modify a JSON data.
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 NVARCHAR type.

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

1. ISJSON -  

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

Syntax - ISJSON (string_value)

Example1 - Passed a valid JSON string

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

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 functions is use to extract scalar value from JSON string, but before getting a value it checks whether the JSON string is valid or not. If 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 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 name, If there is a list of employee 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 above JSON string there are 2 records which is in form of 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 functions extract 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 given JSON string and then returns the new JSON string.

Syntax -

JSON_MODIFY(expression, path, new value)

Example 1 - Update the dept of first record in belos 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 use to convert JSON data into one or multiple rows.

Syntax - OPENJSON(json_expression)

Example - Select all values from 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 -

key value type
name Ram 1
age 40 1
empid T112 1
dept D1 1
location Bangalore 1


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 -

Employee Name Age EmpId Department City
Ram 40 T112 D1 Bangalore


Hope you like this blog. Keep following this blog

You may like other blogs -

Interview Questions and Answers Series -

MVC Interview Questions and Answers
Web API interview questions and answers

Keep following - SharePointCafe.Net

Comments

Popular

What is Web API? When should we use in our projects?