How to work with JSON data on SQL Server

DO you know how to write a SQL script to update a column that stores JSON data. Not the whole column, only one JSON property's value?

Well, last week I had to do it and thanks to the JSON_MODIFY() function, it was a breeze.

Let's see how it works.

Problem

Supppose you have a SQL table with two columns:

• "ID" of type INT
• "Data" of type NVARCHAR

The "Data" column holds a JSON Object and your goal is to update just a single property of the JSON object.

How do you do it?

I didn't have a clue, but luckly, one of my coworkers is a SQL wizard and in less than a few minutes he had a working code snippet in which he intruduced me to the JSON_MODIFY() function which we are going to cover in this post.

Setup

Straight to the code.

First, let's declare a table variable and insert some values into it.

The table we just created

What we just did:

• declared a variable called "myTable" with two columns
  • ID of type INT
  • Data of type NVARCHAR
• Inserted 1 into column ID and the JSON object into the "Data" column.

Updating a property's value

the updated table

See that the JSON_MODIFY() takes three arguments:

1. The JSON object you want to update
2. The key which value you want to update($.[key])
3. The new value

You can also update an array element within your json object(I'll show how to add arrays on the next section)

Let's say that we have the following json object:

And we want to update the entry 'mobile phones' in the array, we would do:

result:

Updating a property's value is straight forward as you saw on the above example. What about if you need to add another key value pair?

Adding property(key value pair)

The second argument has a strict mode(strict) and a default mode(lax), by default if you try to update a key that does not exist, it will create a new one.

If you set it to strict and try to update a key that does not exist, this is what happens:

Adding an array to the JSON object
You can also add an array to the JSON object as follows:

which gives us:

Did you notice the scaped characters? to fix it, pass the new array value into the JSON_QUERY() function. Like so:

You can append a new value to the Array, just specify the same key and pass the option you want to add on the 3rd argument:

Removing properties

To remove, just pass a NULL value as the third argument

Key "Name" and value have been removed

If you need to keep the key and only set the property to NULL, use the strict modifier.

You can also remove(set to NULL or '') an element within an array. To do so, specify the index of the element in the array:

Note: JSON_MODIFY() doesn't support deleting elements from the array. You can only set them to NULL. There are ways to do it which are beyond the scope of this post.

Renaming a key

This is a cool one.

What if we need to change the key "Name" to "ProductName" in our JSON object, how do we do it?

This is the code:

Let's break it up into undertandable steps.

1. Copy the value of the old key to the new key
   • using the JSON_VALUE() function.
   • You pass the json object and the key, it spits out the value.
2. Create a new "key"
   • We are creating a new key called "ProductName" with the value we copied on step 1.
3. Drop the old key
   • Now step 1 and 2 becomes the first argument to the outer JSON_MODIFY().
   • The JSON object now is the initial JSON object + the "ProductName" key and value pair added to the end.
   • Now what is left to do is to remove the old "Name" key and value.

That's about it for today beautiful people.

If you got something out of this post consider:

• Following me on Twitter for extra bits of goodness
• Here on Dev.to @gustavupp for more articles
• Say hi on the comments.

Gus out.