Practical .NET

Retrieving and Updating JSON Objects in SQL Server 2016

With SQL Server 2016, you can store JSON objects in your rows. Here’s how to work with JSON objects, including how to update them once you’ve found them.

• By Peter Vogel
• 11/14/2017

In a previous Practical .NET column I showed how to retrieve rows that hold JSON objects by querying on values in those objects’ properties. However, sometimes you’ll want to work with the complete JSON object rather than with individual values. SQL Server supports that, too … including updating those object’s properties once you retrieve them.

Working with Arrays
For example, you might have an array of JSON stored in an NVarChar column, rather than an individual object as I discussed in my previous column. That array might look like this:

[{"Name":{"First": "Peter", 
 	  "Last": "Vogel"}, 
 "IsPremium": true, 
 "SignupDate":"2017-09-30"},
{"Name":{"First": "Jan", 
 	  "Last": "Irvine"}, 
 "IsPremium": false, 
 "SignupDate":"2015-01-14"}]

The JSON path expressions I discussed in my previous column allow you to refer to an object in the array using a zero-based index enclosed in brackets ([]). A query to retrieve the first name of the second item in my sample array would look like this:

Select JSON_VALUE(LastUpdate, '$[1].Name.First') AS FirstName
From Customers

Using the array syntax to access a column that doesn’t contain an array or to retrieve a position in the array that doesn’t exist will return either NULL or an error, depending on whether you’re in lax or strict mode (the default is lax so my previous query would return NULL if either the Name or First properties don’t exist). This query would generate an error because I’m using strict mode and there’s no object at the 10th position in the array:

Select JSON_VALUE(LastUpdate, 'strict $[10].Name.First') AS FirstName
From Customers

Retrieving JSON Objects
If what you’re interested in retrieving is a complete JSON object, you can retrieve JSON fragments using the JSON_QUERY function. For these examples, I’ll assume I have a LastUpdate column in my row containing this JSON object:

{"Name":{"First": "Peter", 
 	  "Last": "Vogel"}, 
 "IsPremium": true, 
 "SignupDate":"2017-09-30"}

This query returns the JSON fragment in my JSON object’s Name property:

Select JSON_QUERY(LastUpdate, '$.Name')
From Customers

The result is this string:

{"First": "Peter", "Last": "Vogel"}

Unlike JSON_VALUE, which requires a path expression, with JSON_QUERY you can omit the path expression to retrieve the entire contents of the column. The result, though, is the equivalent of just referencing the column. These two queries, for example, return identical results:

Select JSON_QUERY(LastUpdate) As JSON
From Customers

Select LastUpdate As JSON
From Customers

Strings vs. Objects
JSON_QUERY doesn’t actually return a JSON object -- the function is only returning a string. Using JSON_QUERY does, however, prevent the string representation of your JSON object from being escaped (that is, having backslashes inserted in front of each of your double quotes). Because JSON_QUERY returns a string, using JSON_QUERY in a Where clause is … challenging: The JSON object you might be comparing has to be a character-to-character match to the JSON fragment that JSON_QUERY returns.

The following query, for example, will only work if I’m very careful with the contents of the string on the right side of the equals sign (=). For example, inserting a stray blank space around either of the colons (:) would cause the comparison to fail:

Select JSON_QUERY(LastUpdate, '$.Name') As JSON
From Customers
Where JSON_QUERY(LastUpdate, '$.Name') = '{"First": "Peter", "Last": "Vogel"}'

You can pass the output of a JSON_QUERY to the OPENJSON function to have the fragment converted to a virtual table, effectively creating a table from a column in the row. You can then join that virtual table to its row using CROSS APPLY. This query, for example, joins the JSON objects in the LastUpdate column to its table and, based on the customer’s last name, finds those rows where the name in the row and the name in the JSON object don’t match:

Select c.CustomerId
From Customers As c
  
  Cross Apply OPENJSON(JSON_QUERY(LastUpdate, '$.Name')) WITH (Last nvarchar(20)) AS JSONTable
Where JSONTable.Last <> c.LastName

Of course, that result could also be achieved with this simpler query:

SELECT CustomerId
FROM Customers
WHERE (JSON_VALUE(LastUpdate, '$.Name.Last') <> LastName)

Updating JSON
If you want to retrieve a JSON object so you can change a value in one of its properties, JSON_MODIFY lets you do that. You must pass the JSON_MODIFY function the JSON object, a JSON path expression to the property you want to change and the value to be inserted into the property. The function returns the updated JSON object, which, in an Update statement, you can use to change the value of your JSON column.

This query sets the IsPremium property of every Customer’s JSON object in the LastUpdate column to false while updating the row’s LastUpdate column with the changed JSON object:

Update Customers
Set LastUpdate = JSON_MODIFY(LastUpdate, '$.IsPremium', 'false')

You can also add a new object to JSON arrays by adding the "append" keyword in front of your path. This query adds a new value to the SignupDates array:

Update Customers
Set LastUpdate = JSON_MODIFY(LastUpdate, 'append $.SignupDates', '2017-02-28')

JSON’s "flexibility" (for want of a better word) can trip you up here. If the JSON object’s SignupDates property doesn’t contain an array, nothing will be added; If the JSON object doesn’t have a SignupDates property, then one will be added, holding an array with one value in it. Because my JSON object doesn’t have a SignupDates property, my previous update statement will give me this:

{"Name":{"First": "Peter", 
 	  "Last": "Vogel"}, 
 "IsPremium": true, 
 "SignupDate":"2017-09-30",
 "SignupDates":["2017-02-28"]}

As that discussion suggests, you can also use append to add a new property to a JSON object.

As AJAX becomes more prevalent, JSON is only going to become more important. Armed with the tools I’ve discussed in this and previous columns, you can treat your JSON data as JSON data, even in your relational database.