CREATE TABLE dmo_person (
per_id RAW(16) CONSTRAINT NN_per_id NOT NULL,
per_name VARCHAR2(128),
per_tags CLOB CONSTRAINT dmo_pers_json_0 CHECK (per_tags IS JSON),
CONSTRAINT sko_person_pk_0 PRIMARY KEY (per_id)
)
insert into dmo_person
( per_id, per_name, per_tags )
values
(
sys_guid(),
'John Doe',
'{ "perm_admin" : 1, "perm_fileuser" : 0, "perm_subcon" : 1} '
)
DECLARE
v_pertags dmo_person.per_tags%TYPE;
v_json_obj JSON_OBJECT_T;
v_new_pertags dmo_person.per_tags%TYPE;
BEGIN
SELECT per_tags
INTO v_pertags
FROM dmo_person; --where clause
v_json_obj := TREAT(json_element_t.PARSE(v_pertags) AS json_object_t);
v_json_obj.PUT('perm_bigboss' , 1);
v_json_obj.PUT('perm_admin', 0);
v_new_pertags := v_json_obj.to_string;
UPDATE dmo_person
SET per_tags = v_new_pertags; --where clause
END;
select * FROM dmo_person