How to work with JSON data on SQL Server
Tags: webdev, sql, database, programming
Published: 2022-12-13 20:55:52

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oc01hvlpcsfaaai1q5nz.png)

**The table we just created**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2e47858ifzn95q58hana.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xv2yuy3tdw1aadrplrow.png)

**the updated table**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qg7gg38ol9ck67szzarp.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vvkrfc3xpcd8qyxeepbr.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tc5r6acy0nz0hv6z4h2i.png)

result:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2uxb7o74b76kmir92kmx.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p9d7fzy5h9n4a4b9a5f1.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/imng2z9e0ku8jtihg1l1.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fi0n3jbdyn1uwy8ccdz2.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g9b1wwf8yf4k6x1f52yc.png)

which gives us:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/db01ygzs7g3hqyurwoxy.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sow1v3oh64731voanlvi.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rexqaal0gitr2zxyk27n.png)


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zynqvhd1w4qkriq2ns6b.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kkcva4ihvp6vfsfw648e.png)

##Removing properties

To remove, just pass a NULL value as the third argument
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/emru4sawt8jn0g0ptlnd.png)

Key "Name" and value have been removed
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i67gsh48gq8huda718ux.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o2sauqx131g22pcffsp8.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ez38z8xdgg24ud66jqzc.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gi4vhgf27si3ue05herh.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e3sm170eq6ch2xlos6wk.png)

**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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/arb6rqtwia9hpu08hg6b.png)

Let's break it up into undertandable steps.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9m1k1t06x32it8gqojc5.png)

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](https://twitter.com/gus_ppereira) for extra bits of goodness
- Here on Dev.to @gustavupp for more articles
- Say hi on the comments.

Gus out.