翻譯頁面
本頁面由英文在 AI 協助下翻譯而成。若兩者有出入,請以英文版本為準。
JSON_REPLACE
依據路徑更新 JSON 物件中的現有值。JSON_REPLACE 不會在文件中新增鍵,只會取代現有鍵的值。
另請參閱:
[SELECT] [JSON_EXTRACT] [JSON_SET] [JSON_INSERT]
語法
SELECT JSON_REPLACE(json_column, path, new_value) FROM <multipartIdentifier>;
引數
json_column
要在其中取代值的 JSON 資料行或運算式。
path
指向 JSON 文件中要進行取代之位置的 JSON 路徑運算式。路徑必須指向現有的鍵。
new_value
用來取代 JSON 文件中指定路徑上之值的新值。
傳回值
傳回已取代指定值的 JSON 文件。如果指定的路徑不存在,則傳回未變更的原始 JSON 文件。
鍵已存在時的行為
| 函式 | 已存在時是否覆寫? | 不存在時是否建立? |
|---|---|---|
json_insert() | 否 | 是 |
json_replace() | 是 | 否 |
json_set() | 是 | 是 |
範例
取代 JSON 物件中的單一值
SELECT sku as original,
JSON_REPLACE(sku, '$.name', 'Standard_EU') as updated
FROM azure.container_registry.registries
WHERE registry_name = 'exampleRegistryProd'
AND subscription_id = '12345678-abcd-1234-abcd-1234abcd5678'
AND resource_group_name = 'exampleResourceGroupProd';
/* expected output...
|---------------------------------------|------------------------------------------|
| original | updated |
|---------------------------------------|------------------------------------------|
| {"name":"Standard","tier":"Standard"} | {"name":"Standard_EU","tier":"Standard"} |
|---------------------------------------|------------------------------------------|
*/
移除 JSON 物件中的鍵並取代一個值
- 查詢
- 輸出(JSON)
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.network_interfaces
where subscription_id = '0123456789'
and resource_group_name = 'vm-rg'
and network_interface_name = 'vm1450_z1'
[{"afterchange":
{
"disableTcpStateTracking": false,
"dnsSettings": {
"appliedDnsServers": [],
"dnsServers": [],
"internalDomainNameSuffix": "123ylkvekgslelowidrppwubzmb.bx.internal.cloudapp.net"
},
"enableAcceleratedNetworking": false,
"ipConfigurations": [
{
"name": "vmss-flex-vnet-nic01-defaultIpConfiguration",
"properties": {
"publicIPAddress": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/publicIPAddresses/vm1-ip",
"properties": {
"deleteOption": "Delete"
}
},
"subnet": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/virtualNetworks/vm1-vnet/subnets/default"
}
}
}
],
"networkSecurityGroup": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/networkSecurityGroups/vm1-nsg"
}
},
"beforechange":
{
"allowPort25Out": false,
"auxiliaryMode": "None",
"auxiliarySku": "None",
"disableTcpStateTracking": false,
"dnsSettings": {
"appliedDnsServers": [],
"dnsServers": [],
"internalDomainNameSuffix": "123ylkvekgslelowidrppwubzmb.bx.internal.cloudapp.net"
},
"enableAcceleratedNetworking": false,
"enableIPForwarding": false,
"hostedWorkloads": [],
"ipConfigurations": [
{
"etag": "123-bc7e-4609-9328-709fe04c69c3",
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/networkInterfaces/vm1450_z1/ipConfigurations/ipconfig1",
"name": "ipconfig1",
"properties": {
"primary": true,
"privateIPAddress": "10.0.0.4",
"privateIPAddressVersion": "IPv4",
"privateIPAllocationMethod": "Dynamic",
"provisioningState": "Succeeded",
"publicIPAddress": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/publicIPAddresses/vm1-ip",
"properties": {
"deleteOption": "Delete"
}
},
"subnet": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/virtualNetworks/vm1-vnet/subnets/default"
}
},
"type": "Microsoft.Network/networkInterfaces/ipConfigurations"
}
],
"macAddress": "23-0D-3A-1B-FF-91",
"networkSecurityGroup": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Network/networkSecurityGroups/vm1-nsg"
},
"nicType": "Standard",
"primary": true,
"provisioningState": "Succeeded",
"resourceGuid": "566a1d71-923d-41e0-bbd5-d09bf64cbe7b",
"tapConfigurations": [],
"virtualMachine": {
"id": "/subscriptions/0123456789/resourceGroups/vm-rg/providers/Microsoft.Compute/virtualMachines/vm1"
},
"vnetEncryptionSupported": false
}
}]