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?