Upsert JSON documents in SQL Server 2016

First published on MSDN on Mar 03, 2016
In Sql Server 2016 you can easily insert or update JSON documents by combining OPENJSON and MERGE statement. In this example I will create one procedure that imports events formatted as JSON in Events table.

Example code is shown in the following listing. I'm creating one table that will contain events and procedure that will de-serialize events formatted as JSON and upsert them into destination table.
-- Drop sp_ImportEvents stored procedure
DROP PROCEDURE IF EXISTS dbo.sp_ImportEvents
GO

-- Drop Events table
DROP TABLE IF EXISTS dbo.Events
GO

-- Create Events table
CREATE TABLE dbo.Events(
EventId int PRIMARY KEY,
DeviceId int NOT NULL,
Value int NOT NULL,
Timestamp datetime2(7) NULL
)
GO
-- Create sp_InsertEvents stored procedure
CREATE PROCEDURE dbo.sp_ImportEvents
@Events NVARCHAR(MAX)
AS
BEGIN
MERGE INTO dbo.Events AS A
USING (
SELECT *
FROM OPENJSON(@Events) WITH (EventId int, DeviceId int, Value int, Timestamp datetime2(7))) B
ON (A.EventId = B.EventId)
WHEN MATCHED THEN
UPDATE SET A.DeviceId = B.DeviceId,
A.Value = B.Value,
A.Timestamp = B.Timestamp
WHEN NOT MATCHED THEN
INSERT (EventId, DeviceId, Value, Timestamp)
VALUES(B.EventId, B.DeviceId, B.Value, B.Timestamp);
END
GO



Events are provided as JSON array (or single JSON object) that has EventId, DeviceId, Value, and Timestamp properties. OPENJSON will open this document and transform it to table results. Then you can provide this resultset to MERGE statement where you can insert new row or update existing based on the Id column.

Now you can easily import one or many JSON documents into Event table:
EXEC sp_ImportEvents N'{"EventId":1,"DeviceId":17,"Value":35,"Timestamp":"2015-10-23 12:45:37.1237"}'
SELECT * FROM Events

GO

EXEC sp_ImportEvents
N'[{"EventId":1,"DeviceId":17,"Value":37,"Timestamp":"2015-10-23 12:45:37.1237"},
{"EventId":2,"DeviceId":17,"Value":35,"Timestamp":"2015-10-23 12:45:37.1237"}]'
SELECT * FROM Events