Halaman ini diterjemahkan dari bahasa Inggris dengan bantuan AI. Jika keduanya berbeda, versi bahasa Inggris menjadi acuannya.
JSON_REPLACE
Memperbarui nilai yang sudah ada dalam objek JSON berdasarkan jalurnya. JSON_REPLACE tidak menambahkan kunci baru ke dokumen; fungsi ini hanya mengganti nilai dari kunci yang sudah ada.
Lihat juga:
[SELECT] [JSON_EXTRACT] [JSON_SET] [JSON_REPLACE]
Sintaksis
SELECT JSON_REPLACE(json_column, path, new_value) FROM <multipartIdentifier>;
Argumen
json_column
Kolom atau ekspresi JSON yang nilainya akan diganti.
path
Ekspresi jalur JSON yang menunjuk ke lokasi di dalam dokumen JSON tempat penggantian dilakukan. Jalur harus menunjuk ke kunci yang sudah ada.
new_value
Nilai baru yang akan menggantikan nilai pada jalur yang ditentukan dalam dokumen JSON.
Nilai yang dikembalikan
Mengembalikan dokumen JSON yang telah dimodifikasi, dengan nilai yang ditentukan sudah diganti. Jika jalur yang ditentukan tidak ada, dokumen JSON asli dikembalikan tanpa perubahan.
| Fungsi | Timpa jika sudah ada? | Buat jika belum ada? |
|---|---|---|
json_insert() | Tidak | Ya |
json_replace() | Ya | Tidak |
json_set() | Ya | Ya |
Contoh
Mengganti satu nilai dalam objek 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"} |
|---------------------------------------|------------------------------------------|
*/
Mengganti beberapa nilai dalam objek JSON
- Kueri
- Output (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": "02ylkvekgslelowidrppwubzmb.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
}
}]