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