If you’ve been using the JSON_MODIFY() function to modify JSON documents in SQL Server, you might be used to modifying the value part of a key/value property. But did you know that you can also modify the key part?

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

Examples below.

Basic Example

Here’s a basic example to show 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 prints out the original key/value pair, followed by the new key/value pair.

Although we can say that we “renamed” the key, we actually just created a new key, copied the existing value to that new key, then deleted the old key by setting it to NULL.

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

Numeric Values

You need to be careful when copying the data to the new key. By default, SQL Server will enclose it in double quotes. This may or may not be what you want.

However, if you’re copying a numeric value, chances are you want it to remain a numeric value (i.e. without double quotes). In this case you’ll need to use the CAST() function to cast it as 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 resulting value is a number.

If we remove the CAST() function from that example, we end up with this:

-- 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

Result:

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

So in this case, we didn’t just rename the key, we also changed the (JSON) data type from a number to a string.

Note that JSON doesn’t distinguish between different numeric types. It has only one numeric type: number.

Keys with Spaces

In this example, I rename an existing key to a new key that contains a space (it consists of two words, separated by a space).

Because the new key contains a space, I need to surround the key with double quotes. If I don’t do this an error 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 property is nested, no problem. Simply 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 might also have noticed that this example uses the JSON_QUERY() function to extract the value, instead of JSON_VALUE() like in the previous examples.

This is because in this case we’re extracting an array and JSON_VALUE() can’t extract a whole array (it can only extract a scalar value from the array). The JSON_QUERY() function, on the other hand, extracts objects and arrays, but not scalar values.

To read more about this, see JSON_QUERY() vs JSON_VALUE(): What’s the Difference?