Search code examples
oraclecloboracle19c

How to Update CLOB value in Oracle

I have some issue here to update some value inside a clob data type. I am sharing some code about my situation. Please guide me is there any way to to write some update statement to update this?

CREATE TABLE TEST_CLOB
  ( ID NUMBER(10,0), 
    IMPORTDATA CLOB
  );

insert into test_clob values (1001,'{"ClassId":30074,"Attributes":[{"Name":"ID","Value":"265794"},{"Name":"HREF","Value":"-1"},{"Name":"HPRECISION","Value":"5"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO-METHOD","Value":"96"},{"Name":"GEO-PRECISION","Value":"5"},{"Name":"GEO-VISIBILITY","Value":"0"},{"Name":"REMARK","Value":"-1"}]}');
insert into test_clob values (1002,'{"ClassId":30000,"Attributes":[{"Name":"ID","Value":"265756"},{"Name":"HREF","Value":"unknown"},{"Name":"HPRECISION","Value":"4"},{"Name":"HMETHOD","Value":"90"},{"Name":"GEO-METHOD","Value":"0"},{"Name":"GEO-PRECISION","Value":"1"},{"Name":"GEO-VISIBILITY","Value":"0"},{"Name":"REMARK","Value":"-1"}]}');


SELECT id,HMETHOD,HPRECISION, HREF
        FROM   (
          select id,
                 name,
                 value
          from   test_clob tt1
                 CROSS APPLY JSON_TABLE
                 (
                   tt1.IMPORTDATA,
                   '$'
                   COLUMNS 
                   (
                     classid NUMBER PATH '$.ClassId',
                     NESTED PATH '$.Attributes[*]' COLUMNS 
                     (
                       name  VARCHAR2(20) PATH '$.Name',
                       value VARCHAR2(20) PATH '$.Value'
                     )
                   )
                 ) 
        )
        PIVOT 
        (
                MAX(value) FOR name IN 
                (
                  'HMETHOD' AS HMETHOD,
                  'HPRECISION' AS HPRECISION,
                  'HREF' AS HREF
                )
        );

Here it can display my data from above sql. I am giving the screenshot taken from fiddle.

enter image description here

Now my problem is to update HREF value from "unknown" to -1 for the whole table.

I am giving the fiddle link as well if anyone need to use there.

FIDDLE_CLOB

Solution

  • To update the table, you can use:

    UPDATE test_clob
SET importdata = JSON_TRANSFORM(
                   importdata,
                   REPLACE '$.Attributes[*]?(
                     @.Name == "HREF"
                     && @.Value == "unknown"
                   ).Value' = '-1' 
                 )
WHERE JSON_EXISTS(
        importdata,
        '$.Attributes[*]?(@.Name == "HREF" && @.Value == "unknown")'
      );

    fiddle