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!

CodePudding user response：

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

CodePudding user response：

You can split the array into rows the use UNION ALL to add another row and re-aggregate and use JSON_MERGEPATCH to update the object:

MERGE INTO table_name dst
USING (
  SELECT t.ROWID AS rid,
         a.new_value
  FROM   table_name t
         CROSS JOIN LATERAL (
           SELECT JSON_OBJECT(
                    KEY 'sections' VALUE JSON_ARRAYAGG(value FORMAT JSON)
                  ) AS new_value
           FROM   (
             SELECT value
             FROM   JSON_TABLE(
                      t.configuration,
                      '$.sections[*]'
                      COLUMNS value CLOB FORMAT JSON PATH '$'
                    )
             UNION ALL
             SELECT EMPTY_CLOB() || '{"active":false,"code":"page.body"}' FROM DUAL
           )
         ) a
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE
  SET configuration = JSON_MERGEPATCH(dst.configuration, src.new_value);

Which, for the sample data:

CREATE TABLE table_name (configuration CLOB CHECK (configuration IS JSON));

INSERT INTO table_name ( configuration )
VALUES ('{"sections":[{"active":true,"code":"page.about"},{"active":true,"code":"page.title"}]}');

Then, after the merge statement:

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