次の方法で共有

Appending JSON arrays using JSON_MODIFY function

  • [アーティクル]

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.

Comments

  • Anonymous
    June 13, 2016
    This one-argument use of JSON_QUERY does not seem documented in MSDN Library. The documentation says JSON_QUERY ( expression , path ) and does not mention that the path is optional.Is JSON_QUERY(expression) just an alias for JSON_QUERY(expression, '$') or is there a difference in semantics?
  • Anonymous
    September 28, 2018
    Great Post, solved an issue I was having and didn't realize.Using your example, If you wanted to add multiple Events, could you do that was one update, or would it need to be multiple updates, one per event?