Oracle 12.1+ Update CLOB String Data With Null

Question

I am using Oracle 12.1+ DB and over the time of period some of data in my table column COKE_INFO of type CLOB gets corrupted.

I have corrected java code to not let that happen but for all historical data I want to set all corrupted data filed to null.

See Table Description

desc t_coke_ogn
Name                   Null     Type         
---------------------- -------- ------------ 
BOTTLE_CODE              NOT NULL VARCHAR2(30) 
COKE_INFO                            CLOB         
LAST_VERSION_CODE               VARCHAR2(30) 

See COKE_INFO data.

 [
  {
    "type": {
      "label": "MIRINDA",
      "code": 15
    },
    "repeatedColumn": {
      "repeatedColumn": {
        "repeatedColumn": "Coke_1633795"
      }
    }
  }
]

repeated_column above is sometime repeated sometime 10 times and some times 2. I want to set it to null like shown below

[
  {
    "type": {
      "label": "MIRINDA",
      "code": 15
    },
    "repeatedColumn": null
  }
]

Not sure how to progress here

Answer 1

You can use json_table() function together with string functions regexp_replace() and replace() :

SELECT replace(regexp_replace(coke_info, '[[:space:]]*'),rcol,'null') as coke_info
  FROM t_coke_ogn
 CROSS JOIN json_table(coke_info, '$.repeatedColumn[*]'
              COLUMNS (rcol VARCHAR2(4000) FORMAT JSON PATH '$'))

or in order to update directly :

MERGE INTO t_coke_ogn t1
USING 
(
SELECT bottle_code , replace(regexp_replace(coke_info, '[[:space:]]*',''),rcol,'null') 
    as coke_info
  FROM t_coke_ogn
 CROSS JOIN json_table(coke_info, '$.repeatedColumn[*]'
              COLUMNS (rcol VARCHAR2(4000) FORMAT JSON PATH '$'))) t2
   ON (t2.bottle_code = t1.bottle_code)         
  WHEN MATCHED THEN 
UPDATE 
   SET t1.coke_info = t2.coke_info 

Demo