Work With JSON Data In SQL

Introduction

In this, article we are going to learn how easily you can insert and update tables rows using JSON Data.

Suppose you have JSON data as given below.

DECLARE @json NVARCHAR(max) = '{ "StudentId" : 1,"FirstName": "John", "LastName": "Smith",
"Age": 25, "DateOfBirth": "2007-03-25T12:00:00" ,"Marks":95}';

Select query to read JSON data

SELECT * FROM OPENJSON(@json)
WITH (StudentId INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), Age int, DateOfBirth DATETIME, Marks INT)

OPENJSON function will parse JSON, and in WITH clause, you can specify what column names you want to see. OPENJSON will match column names with keys in JSON arrays and return a set of rows. Also, it will do the automatic conversion from character data into types that are associated with each column.

Insert JSON data

INSERT INTO Student (StudentId, FirstName, LastName, Age, DateOfBirth, Marks)
SELECT StudentId, FirstName, LastName, Age, DateOfBirth, Marks
FROM OPENJSON(@json)
WITH (StudentId INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), Age INT, DateOfBirth DATETIME, Marks INT)

Update JSON data

UPDATE Student
 SET FirstName = stud.FirstName,
 LastName = stud.LastName,
 Age = stud.Age,
 DateOfBirth = stud.DateOfBirth,
 Marks = stud.Marks
 FROM OPENJSON(@json)
 WITH (StudentId INT,
       FirstName NVARCHAR(50), LastName NVARCHAR(50), 
       Age INT, DateOfBirth DATETIME, Marks INT) AS stud
 WHERE Student.StudentId = stud.StudentId

Conclusion

OPENJSON function will help you to easily transform JSON objects to table rows. It helps you to easily insert/update table rows using JSON text.

You don’t need to parse JSON in the application layer and there is no risk of SQL injection attacks if you have valid JSON.

Also, Check Find Specific Text In Stored Procedure