Append Key into JSON_OBJ_COLUMN - Oracle

Question

In Oracle 12c I created one table with json objects.

Imagine the next sample:

id	date	json_obj
1	01-MAY-2021	{ key_1: val1, key_2: val2}

Now I want to update key_2 value, and append key_3: val3 into the same json object related with id 1.

That could be edited? or I must to overwrite?

Answer 1

Provided that your DB version is 12R2+, then you can use PARSE() and PUT() for object type JSON_OBJECT_T consecutively, and then apply a standard UPDATE statement such as

DECLARE
  jobj JSON_OBJECT_T;
  jdoc tab.json_obj%TYPE;
BEGIN
  SELECT json_obj
    INTO jdoc
    FROM tab
   WHERE id = 1;

  jobj := JSON_OBJECT_T.PARSE( jdoc );
  jobj.PUT( 'key_2', 'val_22' );
  jobj.PUT( 'key_3', 'val_3' );  
  jdoc := jobj.TO_CLOB();

  DBMS_OUTPUT.PUT_LINE( jdoc );
  
  UPDATE tab
     SET json_obj = jdoc
   WHERE id = 1;
END;
/