Skip to main content

JSON_REPLACE

Updates existing values in a JSON object based on their path. JSON_REPLACE does not add new keys to the document; it only replaces the values of existing keys.

See also:
[SELECT] [JSON_EXTRACT] [JSON_SET] [JSON_REPLACE]

Syntax​

SELECT JSON_REPLACE(json_column, path, new_value) FROM <multipartIdentifier>;

Arguments​

json_column
The JSON column or expression from which to replace values.

path
The JSON path expression pointing to the location within the JSON document where the replacement should occur. The path must point to an existing key.

new_value
The new value to replace at the specified path in the JSON document.

Return Value(s)​

Returns a modified JSON document with the specified value replaced. If the specified path does not exist, the original JSON document is returned unchanged.

Behavior If a Key Exists
FunctionOverwrite if already exists?Create if does not exist?
json_insert()NoYes
json_replace()YesNo
json_set()YesYes

Examples​

Replace a Single Value in a JSON Object​

SELECT sku as original,
JSON_REPLACE(sku, '$.name', 'Standard_EU') as updated
FROM azure.container_registry.registries 
WHERE registryName = 'exampleRegistryProd' 
AND subscriptionId = '12345678-abcd-1234-abcd-1234abcd5678' 
AND resourceGroupName = 'exampleResourceGroupProd';
/* expected output...
|---------------------------------------|------------------------------------------|
|               original                |                 updated                  |
|---------------------------------------|------------------------------------------|
| {"name":"Standard","tier":"Standard"} | {"name":"Standard_EU","tier":"Standard"} |
|---------------------------------------|------------------------------------------|
*/

Replace Multiple Values in a JSON Object​

select
  properties beforechange,
  json_replace(json_remove(properties,
              '$.allowPort25Out',
              '$.auxiliarySku',
              '$.provisioningState',
              '$.resourceGuid',
              '$.macAddress',
              '$.vnetEncryptionSupported',
              '$.enableIPForwarding',
              '$.defaultOutboundAccess',
              '$.primary',
              '$.virtualMachine',
              '$.hostedWorkloads',
              '$.tapConfigurations',
              '$.nicType',
              '$.auxiliaryMode',
              '$.ipConfigurations[0].id',
              '$.ipConfigurations[0].etag',
              '$.ipConfigurations[0].type',
              '$.ipConfigurations[0].properties.provisioningState',
              '$.ipConfigurations[0].properties.privateIPAddress',
              '$.ipConfigurations[0].properties.privateIPAllocationMethod',
              '$.ipConfigurations[0].properties.primary',
              '$.ipConfigurations[0].properties.privateIPAddressVersion'
            ),
  '$.ipConfigurations[0].name',
  'vmss-flex-vnet-nic01-defaultIpConfiguration'
  ) afterchange
from
  azure.network.interfaces
where subscriptionId = '0123456789'
and resourceGroupName = 'vm-rg'
and networkInterfaceName = 'vm1450_z1'