In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-07 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
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!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.