Adding elements in the end to a json array of objects in Oracle

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!

Answer

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