First published on MSDN on Sep 22, 2015
Introduction
In SQL Server 2016 will be added a new table value function that can transform JSON text into row(s). In this post I will show how you can directly insert/update table rows using JSON objects.
Problem
Our database receives JSON documents from external systems. These JSON documents might be values sent to REST web services, information from sensors, records from log files formatted as JSON, or other system transform information as JSON during data transfer. We need a way to import JSON documents into SQL tables.
Solution
OPENJSON table value function transforms JSON object to one or many rows. These rows can be imported into tables or they can be used to update existing records in tables.
Syntax of OPENJSON function that transforms JSON document to rowset looks like:
OPENJSON(<json text>)
WITH ( <<column/mapping definitions>> )
In the WITH clause you can define schema of the returned rows with names of columns and their types. OPENJSON function will parse JSON object, match properties in JSON object with column names and convert their values to specified types. Example of query that opens content of JSON variable is shown in the following listing:
declare @json nvarchar(max) = '{
"id" : 2,
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"dateOfBirth": "2015-03-25T12:00:00",
"spouse": null
}';
SELECT *
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
isAlive bit, age int,
dateOfBirth datetime2, spouse nvarchar(50))
In this example, we have opened json object and mapped fields to columns by name. Result of execution would look like:
|
id
|
firstName
|
lastName
|
isAlive
|
age
|
dateOfBirth
|
spouse
|
|
2
|
John
|
Smith
|
1
|
25
|
2015-03-25T12:00:00
|
NULL
|
OPENJSON function will also work with JSON arrays. In that case, it will return a multi-row table (each table row will match one JSON object in array) instead of single row table. This function can also open nested/hierarchical JSON object (I will describe this in a separate post). In this post you will see how easily you can insert and update tables using JSON objects.
Use Case 1: Inserting JSON content in table
Now, when we have opened JSON we can do anything. As an example, we can use OPENJSON function to directly load JSON object into a table:
declare @json nvarchar(max) = '{
"id" : 2,
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"dateOfBirth": "2015-03-25T12:00:00",
"spouse": null
}';
INSERT INTO Person
SELECT *
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
isAlive bit, age int,
dateOfBirth datetime2, spouse nvarchar(50))
JSON variable will be converted to a row that can be inserted into a table as any other row. You can use this approach to directly load JSON objects received via REST service without need to transform JSON to object model, set values as parameters in SQL command etc. This way, you can directly send single JSON text file and load it into table. If your JSON is valid there is no risk that you will have some SQL injection attack. OPENJSON will not execute any command - it just returns a table row if JSON text is properly formatted.
You can also insert an array of JSON rows because the same code will work. OPENJSON will just return set of rows instead of single row that should be inserted.
Use Case 2: Updating table row using JSON object
We can easily update any table based on JSON text. You just need to pass entire JSON object, open it using OPENJSON function and update table row as shown in the following example:
declare @json nvarchar(max) = N'{
"id" : 2,
"firstName": "George",
"lastName": "Smith",
"isAlive": true,
"age": 31,
"dateOfBirth": "2015-03-25T12:00:00",
"spouse": "Marry"
}';
UPDATE Person
SET firstName = json.firstname,
lastName = json.lastname,
isAlive = json.isAlive,
age = json.age,
dateOfBirth = json.dateOfBirth,
spouse = json.spouse
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
isAlive bit, age int,
dateOfBirth datetime2, spouse nvarchar(50)) AS json
WHERE Person.id = json.id
In this example we have opened JSON variable and updated columns in the Person table. We have used id field from the opened JSON to match the row that should be updated, while the other fields are used to update Person row.
Use Case 3: Delete table row using information in JSON object
I don't believe that you will send JSON object if you want to delete it (probably you will directly send id of table row). However, even if you decide to use this approach, you can delete row in the table using following example:
DELETE Person
WHERE id = CAST(JSON_VALUE(@json, '$.id') AS int)
JSON_VALUE function will take id on the path $.id and row in the table will be deleted. You can use OPENJSON instead of JSON_VALUE, but this is simpler approach.
Conclusion
OPENJSON function will help you to easily parse, open and transform JSON object to table rows. Using OPENJSON you can easily insert or update table rows using JSON text.
You don't need to parse JSON in application layer, you don't need to pass properties as SQL parameters, there is no risk of SQL injection attacks if you have valid JSON.
This is probably easiest and most secure method to import your JSON documents in Sql Server.
