DEV Community

Cover image for How to work with JSON data on SQL Server
Gus Pear 🍐
Gus Pear 🍐

Posted on • Updated on


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.


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.


Straight to the code.

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

Image description

The table we just created
Image description

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

Image description

the updated table
Image description

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:
Image description

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

Image description

Image description

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)

Image description

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:

Image description

Image description

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

Image description

which gives us:
Image description

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

Image description

Image description

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:
Image description

Image description

Removing properties

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

Key "Name" and value have been removed
Image description

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

Image description

Image description

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:

Image description

Image description

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:
Image description

Let's break it up into undertandable steps.

Image description

  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 @gustavupp for more articles
  • Say hi on the comments.

Gus out.

Top comments (0)

50 CLI Tools You Can't Live Without

>> Check out this classic DEV post <<