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

Question

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!

what version of oracle are you using ?

Roberto Hernandez
– Roberto Hernandez

2021-10-24 13:18:05 +00:00
Commented Oct 24, 2021 at 13:18 — Roberto Hernandez
– Roberto Hernandez, Commented Oct 24, 2021 at 13:18
19c version....

danda
– danda

2021-10-24 13:31:38 +00:00
Commented Oct 24, 2021 at 13:31 — danda
– danda, Commented Oct 24, 2021 at 13:31
JSON_MODIFY is not a function in Oracle, but in SQL Server

Roberto Hernandez
– Roberto Hernandez

2021-10-24 13:47:15 +00:00
Commented Oct 24, 2021 at 13:47 — Roberto Hernandez
– Roberto Hernandez, Commented Oct 24, 2021 at 13:47

MT0 · Accepted Answer · 2021-10-24 22:17:57Z

1

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

answered Oct 24, 2021 at 22:17

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2021-10-25 00:30:23Z

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

ludodba · Accepted Answer · 2024-08-13 11:41:21Z

0

This should work from 19c onwards:

UPDATE TABLE_NAME
SET configuration = JSON_TRANSFORM(
  configuration, APPEND '$.sections' = JSON('{"active":false,"code":"page.body"}')
)
WHERE ...

answered Aug 13, 2024 at 11:41

ludodba

12 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related