How to rename JSON key in SQL Server (T-SQL)?

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

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

Here's a basic example of what I mean.

-- Declare a variable and assign some JSON to it
DECLARE @data NVARCHAR(50)='{"Name":"Homer"}'

-- Print the current JSON
PRINT @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 JSON
PRINT @data

Result:

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

This will print out the original key/value pairs, followed by the new key/value pairs.

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

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

Number

Need to 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 probably want it to still be a numeric value (i.e. without 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 it
DECLARE @data NVARCHAR(50)='{"Residents":768}'

-- Print the current JSON
PRINT @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 JSON
PRINT @data

Result:

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

So the result is a number.

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

-- Declare a variable and assign some JSON to it
DECLARE @data NVARCHAR(50)='{"Residents": 768}'

-- Print the current JSON
PRINT @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 JSON
PRINT @data

The result:

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

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

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

key keys and spaces

In this example, 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 surround the key with double quotes. If you don't do this, errors will occur.

-- Declare a variable and assign some JSON to it
DECLARE @data NVARCHAR(50)='{"Population":68}'

-- Print the current JSON
PRINT @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 JSON
PRINT @data

Result:

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

Nested properties

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

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

Result:

{ 
"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 the value instead of the previous one Use JSON_VALUE() as an example.

This is because in this case, we are extracting an array, and 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.

Related recommendations: "SQL Tutorial" "MySQL Tutorial"

The above is the detailed content of How to rename JSON key in SQL Server (T-SQL)?. For more information, please follow other related articles on the PHP Chinese website!