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?

Barbaros Özhan · Accepted Answer · 2021-05-21 21:30:00Z

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;
/

answered May 21, 2021 at 21:30

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Append Key into JSON_OBJ_COLUMN - Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related