The method of renaming JSON key in SQL Server

Editor to share with you how to rename the JSON key in SQL Server, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

If you have been using the JSON_MODIFY () function to modify the JSON document in SQL Server, you may be used to modifying the value part of the key/value property. But did you know that you can also modify the key section?

The trick is to copy the value to a new key and then delete the old key.

Here is a basic example to illustrate what I mean.

-- Declare a variable and assign some JSON to itDECLARE @ data NVARCHAR (50) ='{"Name": "Homer"}'--Print the current JSONPRINT @ data-- Rename the key (by copying the value to a new key, then deleting the old one) SET @ data= JSON_MODIFY (JSON_MODIFY (@ data,'$.Handle', JSON_VALUE (@ data,'$.Name')),'$.Name', NULL)-- Print the new JSONPRINT @ data

Results:

{"Name": "Homer"} {"Handle": "Homer"}

This prints out the original key / value pair, followed by the new key / value pair.

Although we can say that we "renamed" the key, we just created a new key, copied the existing value to the new key, and then set the old key to NULL to delete it.

In this case, we use the JSON_VALUE () function to extract the value.

Numerical value

Be careful when copying data to a new key. By default, SQL Server encloses it in double quotes. This may or may not be what you want.

However, if you copy a numeric value, you may want it to be still a numeric value (that is, no double quotes). In this case, you need to use the CAST () function to convert it to a numeric data type. Here's an example:

-- Declare a variable and assign some JSON to itDECLARE @ data NVARCHAR (50) ='{"Residents": 768}'--Print the current JSONPRINT @ data-- Rename the key (by copying the value to a new key, then deleting the old one) SET @ data= JSON_MODIFY (JSON_MODIFY (@ data,'$.Population', CAST (JSON_VALUE (@ data,'$.Residents') AS int)),'$.residents', NULL)-- Print the new JSONPRINT @ data

Results:

{"Residents": 768} {"Population": 768}

So the result is a number.

If we remove the CAST () function from this example, we get the following result:

-- Declare a variable and assign some JSON to itDECLARE @ data NVARCHAR (50) ='{"Residents": 768}'--Print the current JSONPRINT @ data-- Rename the key (by copying the value to a new key, then deleting the old one) SET @ data= JSON_MODIFY (JSON_MODIFY (@ data,'$.Population', JSON_VALUE (@ data,'$.Residents')),'$.residents', NULL)-- Print the new JSONPRINT @ data

Results:

{"Residents": 768} {"Population": "768"}

So, in this case, we not only renamed the key, but also changed the (JSON) data type from a number to a string.

Note that JSON does not distinguish between different number types. It has only one numeric type: number.

Key key and space

In this case, I renamed an existing key to a new key that contains spaces (it consists of two words separated by spaces).

Because the new key contains spaces, I need to enclose the key in double quotation marks. If you do not do so, an error will occur.

-- Declare a variable and assign some JSON to itDECLARE @ data NVARCHAR (50) ='{"Population": 68}'--Print the current JSONPRINT @ data-- Rename the key (by copying the value to a new key, then deleting the old one) SET @ data= JSON_MODIFY (JSON_MODIFY (@ data,'$. "Average IQ"', CAST (JSON_VALUE (@ data,'$.Population') AS int)),'$.population', NULL)-- Print the new JSONPRINT @ data

Results:

{"Population": 68} {"Average IQ": 68}

Nested properties

If the properties are nested, there is no problem. Just use dot symbols to refer to it.

DECLARE @ data NVARCHAR (4000) SET @ data=N' {"Suspect": {"Name": "Homer Simpson", "Hobbies": ["Eating", "Sleeping", "Base Jumping"]} 'PRINT @ dataSET @ data= JSON_MODIFY (JSON_MODIFY (@ data,'$.Suspect.Qualifications', JSON_QUERY (@ data,'$.Suspect.Hobbies')),' $.suspect. Hobbies', NULL) PRINT @ data

Results:

{"Suspect": {"Name": "Homer Simpson", "Hobbies": ["Eating", "Sleeping", "Base Jumping"]}} {"Suspect": {"Name": "Homer Simpson", "Qualifications": ["Eating", "Sleeping", "Base Jumping"]}

You may also notice that this example uses the JSON_QUERY () function to extract values instead of using JSON_VALUE () as in the previous example.

This is because in this case, we are extracting an array, while JSON_VALUE () cannot extract the entire array (it can only extract scalar values from the array). On the other hand, the JSON_QUERY () function extracts objects and arrays, but not scalar values.

This is all about the method of renaming the JSON key in SQL Server. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!