I have a json column named "configuration" in an Oracle database with a data like-
{"sections":[{"active":true,"code":"page.about"},{"active":true,"code":"page.title"}...]}
How can add elements to the "sections" array inside the CLOB? for example, add this object to the CLOB- {"active":false, "code":"page.body"}
I tried to do this-
UPDATE *TABLE_NAME*
SET configuration = JSON_MODIFY(configuration, 'append $.sections',JSON_QUERY(N'{"active":false,"code":"page.body"}'))
but I got this error-
Error report -
SQL Error: ORA-00917: missing comma
00917. 00000 - "missing comma"
*Cause:
*Action:
Thanks!
You can create the function:
CREATE FUNCTION json_append_array(
json IN CLOB,
path IN VARCHAR2,
value IN CLOB
) RETURN CLOB
IS
j_obj JSON_OBJECT_T := JSON_OBJECT_T(json);
j_arr JSON_ARRAY_T := j_obj.get_Array(path);
BEGIN
j_arr.append( JSON_OBJECT_T(value) );
RETURN j_obj.to_Clob();
END;
/
Then you can update the table:
UPDATE TABLE_NAME
SET configuration = JSON_APPEND_ARRAY(
configuration,
'sections',
'{"active":false,"code":"page.body"}'
);
Then:
SELECT *
FROM table_name;
Outputs:
CONFIGURATION {"sections":[{"active":true,"code":"page.about"},{"active":true,"code":"page.title"},{"active":false,"code":"page.body"}]}
db<>fiddle here