Oracle数据库中如何将JSON字段内的DataRiferimento键重命名为datarif

Got it, let's figure out how to rename the DataRiferimento key (with surrounding spaces) to datarif in your Oracle table's JSON_CODE column. Below are two practical methods based on your Oracle Database version:

Method 1: Use JSON_TRANSFORM (Oracle 19c+) #

This is the cleanest approach if you're running Oracle 19c or later. The JSON_TRANSFORM function lets you modify JSON data directly, including renaming keys without rebuilding the entire object.

Update Statement: #

UPDATE your_table_name
SET JSON_CODE = JSON_TRANSFORM(
    JSON_CODE,
    RENAME '$. DataRiferimento ' TO '$.datarif'
)
WHERE JSON_EXISTS(JSON_CODE, '$. DataRiferimento '); -- Optional: Only target rows with the key

Test First with SELECT: #

Always verify the output before running an update to avoid mistakes:

SELECT JSON_TRANSFORM(
    JSON_CODE,
    RENAME '$. DataRiferimento ' TO '$.datarif'
) AS updated_json
FROM your_table_name;

Method 2: Reconstruct JSON with JSON_OBJECT (Pre-19c Compatibility) #

If you're on an older version like Oracle 12c, you'll need to rebuild the JSON object by extracting existing values and replacing the target key.

Update Statement: #

UPDATE your_table_name
SET JSON_CODE = JSON_OBJECT(
    'DataElaborazione' VALUE JSON_VALUE(JSON_CODE, '$.DataElaborazione'),
    'DataMovimento' VALUE JSON_VALUE(JSON_CODE, '$.DataMovimento'),
    'datarif' VALUE JSON_VALUE(JSON_CODE, '$. DataRiferimento ')
)
WHERE JSON_EXISTS(JSON_CODE, '$. DataRiferimento ');

Test First with SELECT: #

SELECT JSON_OBJECT(
    'DataElaborazione' VALUE JSON_VALUE(JSON_CODE, '$.DataElaborazione'),
    'DataMovimento' VALUE JSON_VALUE(JSON_CODE, '$.DataMovimento'),
    'datarif' VALUE JSON_VALUE(JSON_CODE, '$. DataRiferimento ')
) AS updated_json
FROM your_table_name;

Key Notes: #

• Replace your_table_name with the actual name of your table.
• The JSON_EXISTS clause is optional but recommended to skip rows that don't contain the DataRiferimento key.
• Oracle's JSON path matching is strict about whitespace—make sure to include the extra spaces around DataRiferimento in all path expressions to match your original data correctly.

内容的提问来源于stack exchange，提问作者Giacomo Albanese