Updating the value of a JSON element to append zeros to itself (inplace update of the value of a json element)

Question

We are using columns to store JSON data in Oracle 19c.

    CREATE TABLE test_table
    (
    test_col                VARCHAR2(32767 BYTE)
    );
    
    ALTER TABLE test_table ADD
    CONSTRAINT CNS4_test_table
      CHECK ( test_col is json with unique keys strict )
      ENABLE VALIDATE;

SET DEFINE OFF;
Insert into test_table
   (test_col)
 Values
   ('{"formatLabel":"999999","value":"345345","newTest":false,"empTest":false,"conTest":false,"indTest":false}');
Insert into test_table
   (test_col)
 Values
   ('{"formatLabel":"1999999999","value":"1545646000","newTest":false,"empTest":false,"conTest":false,"indTest":false}');
Insert into test_table
   (test_col)
 Values
   ('{"formatLabel":"1999999999","value":"1545454611","newTest":false,"empTest":false,"conTest":false,"indTest":false}');
COMMIT;

The requirement is to update "formatLabel" to 0999999999 wherever it is 999999 and the corresponding "value" needs to be appended with 0's to make it 10 digits whenever it is 6 digits. The logic could look something like this:

If LENGTH(json_value(test_col, '$.value')) = 6 THEN '0'||json_value(test_col, '$.value')||'000' 

The below statement is syntactically wrong, but I'm looking for something like this:

UPDATE test_table a
SET    a.test_col = JSON_MERGEPATCH(a.test_col, '{"formatLabel":"0999999999","value":''0''||json_value(test_col, ''$.value'')||''000'' }')
WHERE   json_value(test_col, '$.formatLabel') = '999999' 
and LENGTH(json_value(test_col, '$.value')) = 6;

Can JSON_MERGEPATCH be used to update the elements in-place by appending values to them as above?

Answer 1

I have gone back to the old method of creating a function using JSON_OBJECT_T for resolving this issue:

CREATE OR REPLACE FUNCTION updatefunc(JSON_DOC in VARCHAR2, NEW_VAL in VARCHAR2, NEW_FORMAT in VARCHAR2)
RETURN VARCHAR2
IS
jo JSON_OBJECT_T;
BEGIN
jo := JSON_OBJECT_T(JSON_DOC);
jo.put('value', NEW_VAL);
jo.put('formatLabel', NEW_FORMAT);
RETURN jo.to_string();
END;
/

UPDATE test_table set test_col=updatefunc(test_col, 
'0'||json_value(test_col, '$.value')||'000','0999999999' )
 WHERE     json_value(test_col, '$.formatLabel') = '999999' 
and LENGTH(json_value(test_col, '$.value')) = 6;

COMMIT;

DROP FUNCTION updatefunc;