CODEWITHSUNDEEP
jsonoracle-database
Harish
Harish

Reputation: 21

How to update JSON column in oracle 12.1

Am trying to update a JSON column in oracle 12.1. Is there any json function that can be used to update the JSON. I have added a constraint JSON_COLUMN is JSON.

I have tried : 

UPDATE tablename SET JSON =
json_mergepatch(JSON, '{"STRUCTURE_NAME":null}');

But this function is applicable only for 19c

This "STRUCTURE_NAME": "ABC:STATE:L12345", needs to be updated with "STRUCTURE_NAME":null

Upvotes: 2

Views: 2213

Answers (2)

Bhawana Solanki
Bhawana Solanki

Reputation: 55

Below query will overwrite the JSON document column for all rows.

UPDATE JSON_TABLE
SET JSON_DOC_COL=JSON_OBJECT('name' VALUE var_name,
                             'age' VALUE var_age);

To append the JSON document column, below query can be used.

UPDATE JSON_TABLE
SET JSON_DOC_COL=JSON_MERGEPATCH(JSON_DOC_COL,JSON_OBJECT('name' VALUE var_name,
                             'age' VALUE var_age) RETURNING CLOB);

Upvotes: 0

Chris Saxon
Chris Saxon

Reputation: 9825

Pre-19c, if you want to change any values in a JSON document, you have to replace the whole thing:

create table t (
  doc varchar2(100)
    check ( doc is json ) 
);

insert into t values ('{
  "changeMe" : "to null",
  "leaveMe"  : "alone"
}');

update t
set    doc = '{
  "changeMe" : null,
  "leaveMe"  : "alone"
}';

select * from t;

DOC                                               
{
  "changeMe" : null,
  "leaveMe"  : "alone"
}

Note that when you get to 19c and use json_mergepatch, setting an attribute to null removes it from the document:

update t
set    doc = json_mergepatch ( 
  doc, 
  '{
    "changeMe" : null
  }');

select * from t;

DOC                   
{"leaveMe":"alone"}

Upvotes: 3

Related Questions