First published on MSDN on Jun 10, 2016
Sql Server 2016 and Azure Sql Database enables you to easily modify JSON object and arrays.
JSON_MODIFY
updates the value of a property in a JSON string and returns the updated JSON string. Here I will show how to append objects in JSON array.
In new SQL Server 2016
WideWorldImporters
sample database you can find ReturnedDeliveryData JSON column in Sales.Invoice table. This column contains information about delivery information formatted as JSON like in the following example:
{
"Events": [{
"Event": "Ready for collection",
"EventTime": "2013-01-01T12:00:00",
"ConNote": "EAN-125-1051"
}, {
"Event": "DeliveryAttempt",
"EventTime": "2013-01-02T07:05:00",
"ConNote": "EAN-125-1051",
"Comment": "Receiver not present"
}],
"DeliveredWhen": "2013-01-02T07:05:00",
"ReceivedBy": "Klara Rakus"
}
In the $.Events array we have complete history of events, delivery attempts, etc. If you want to see values in the event array you can use the following query:
select di.*
from Sales.Invoices
cross apply OPENJSON(ReturnedDeliveryData,'$.Events')
WITH (Event nvarchar(100), EventTime datetime2,
ConNote nvarchar(100), Comment nvarchar(100) ) as di
where InvoiceID = 1
Now, imagine that you want to add a new event in the $.Events array. You don't need to parse entire JSON to inject a new object. JSON_MODIFY function enables you to update values on some path if you specify append keyword in JSON Path (2nd parameter):
DECLARE @event nvarchar(4000)
SET @event = N'{"Event":"DeliveryAttempt","EventTime": "2013-02-02T08:15:00","Comment": "Receiver not present"}'
UPDATE Sales.Invoices
SET ReturnedDeliveryData = JSON_MODIFY(ReturnedDeliveryData, 'append $.Events', @event)
WHERE InvoiceID = 1
JSON_MODIFY function will find an array on the path $.Events and append value provided as third parameter. Unfortunately, this code will
not do what we expect
. If you provide just a string variable as third parameter, JSON_MODIFY will treat it as any other string and wrap it with double quotes, escape all special characters so you will have string as the last parameter instead of object - something like:
{
"Events": [{
"Event": "Ready for collection",
"EventTime": "2013-01-01T12:00:00",
"ConNote": "EAN-125-1051"
}, {
"Event": "DeliveryAttempt",
"EventTime": "2013-01-02T07:05:00",
"ConNote": "EAN-125-1051",
"Comment": "Receiver not present"
}, "{\"Event\":\"DeliveryAttempt\",\"EventTime\": \"2013-02-02T08:15:00\",\"Comment\": \"Receiver not present\"}"
],
"DeliveredWhen": "2013-01-02T07:05:00",
"ReceivedBy": "Klara Rakus"
}
This is not what we need because we need to append JSON object and not some escaped JSON string. If you want to append JSON object instead of string that looks like a object, you need to wrap third parameter with JSON_QUERY(...) function:
DECLARE @event nvarchar(4000)
SET @event = N'{"Event":"DeliveryAttempt","EventTime": "2013-02-02T08:15:00","Comment": "Receiver not present"}'
UPDATE Sales.Invoices
SET ReturnedDeliveryData = JSON_MODIFY(ReturnedDeliveryData, 'append $.Events', JSON_QUERY(@event))
WHERE InvoiceID = 1
Since JSON_QUERY returns valid JSON fragment as result, JSON_MODIFY will know that this is not a string and that result should not be escaped. JSON_QUERY without second parameter behaves as "cast to JSON". Now you will have expected result:
{
"Events": [{
"Event": "Ready for collection",
"EventTime": "2013-01-01T12:00:00",
"ConNote": "EAN-125-1051"
}, {
"Event": "DeliveryAttempt",
"EventTime": "2013-01-02T07:05:00",
"ConNote": "EAN-125-1051",
"Comment": "Receiver not present"
}, {"Event":"DeliveryAttempt","EventTime": "2013-02-02T08:15:00","Comment": "Receiver not present"}
],
"DeliveredWhen": "2013-01-02T07:05:00",
"ReceivedBy": "Klara Rakus"
}
As you can see appending JSON object to arrays is easy. You just need to be aware that JSON_MODIFY might convert JSON formatted as string into escaped string if you don't use it properly.
