ORACLE : renaming a key in a json field by oracle

Giacomo Albanese 0
Viewed 48

I have a table in which in one of the columns there is a json data as below

COLUMN NAME : JSON_CODE { "DataElaborazione" : "20220718", "DataMovimento" : "20220531", "DataRiferimento" : "20220715", }

I want to rename DataRiferimento to datarif

expected result

COLUMN NAME : JSON_CODE { "DataElaborazione" : "20220718", "DataMovimento" : "20220531", "datarif" : "20220715", }

see image for structure

enter image description here

thank you

oracle sql json
1 Answers

You can use JSON_TRANSFORM function:

select JSON_TRANSFORM('{ "DataElaborazione" : "20220718", "DataMovimento" : "20220531", "DataRiferimento" : "20220715" }',
                       RENAME '$.DataRiferimento' = 'datarif')
  from dual

Or JSON_OBJECT_T:

declare
  v_json JSON_OBJECT_T;
begin
  v_json := JSON_OBJECT_T('{ "DataElaborazione" : "20220718", "DataMovimento" : "20220531", "DataRiferimento" : "20220715" }');
  v_json.rename_key('DataRiferimento', 'datarif');
  dbms_output.put_line(v_json.stringify);
end;

You can create function and use it in your query:

create or replace function renameJsonKey(p_json    IN VARCHAR2,
                                         p_old_key IN VARCHAR2,
                                         p_new_key IN VARCHAR2) return VARCHAR2 
    RESULT_CACHE RELIES_ON (your_table)
is  
  v_json JSON_OBJECT_T;
begin
  v_json := JSON_OBJECT_T(p_json);
  v_json.rename_key(p_old_key, p_new_key);
  return v_json.stringify;
end;

select renameJsonKey(col, 'DataRiferimento', 'datarif') from your table
Sergey Afinogenov0
Related