Append to JSON_ARRAY in an existing JSON in ORACLE

Question

I'm trying to combine arrays of JSON documents in Oracle 19c.

I'd like to append items to an array in an already existing JSON stored in a table. I know this question has been asked in various forms before (with helper functions and SQL implementations demonstrated) but I'm still having issues trying to pull this together by splitting the array into rows (cross_join_lateral approach) or putting together a proper helper function for Oracle 19c.

I would like to combine the values sections to end up with something like this:

UPDATED_JSON
{"keys":["VAL1","VAL2","VAL3"],"values":[["1","2","3"],["a","b","c"],["1","b","3"],["2","d","f"],["3","b","g"]]}

One additional question: Would it also be possible to order the array values by the first item (val1 in this case)?

Setup Info:

CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
CREATE TABLE tbl2 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
CREATE TABLE tbl3 (json clob, json_updated clob);
INSERT INTO tbl1 VALUES ('1','2','3');
INSERT INTO tbl1 VALUES ('a','b','c');
INSERT INTO tbl1 VALUES ('1','b','3');
INSERT INTO tbl2 VALUES ('2','d','f');
INSERT INTO tbl2 VALUES ('3','b','g');

insert into tbl3
select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3 null on null))) as js, null
from tbl1

select json from tbl3

JSON
{"keys":["VAL1","VAL2","VAL3"],"values":[["1","2","3"],["a","b","c"],["1","b","3"]]}

select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3 null on null))) as js
from tbl2

JS
{"keys":["VAL1","VAL2","VAL3"],"values":[["2","d","f"],["3","b","g"]]}

This outputs the new value correctly, but working on updating the document with it.

SELECT new_value
  FROM   tbl3 t
         CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(json) AS new_value
         FROM   (
           SELECT json
           FROM   JSON_TABLE( t.json, '$.values[*]' COLUMNS (json CLOB FORMAT JSON PATH '$'))
           UNION ALL
           select json_array(val1, val2, val3 null on null returning clob)
           from tbl2
         )
       );

JS
[["1","2","3"],["a","b","c"],["1","b","3"],["2","d","f"],["3","b","g"]]

fiddle

Just add keys property to your current result and use update to update the value in a table. Or use json_mergepatch function available in 19c to update values property: dbfiddle.uk/Crq-lzYS — astentx
– astentx, Commented Aug 30, 2023 at 7:01

nbk · Accepted Answer · 2023-08-28 17:49:25Z

1

You can use json_transform for that

SELECT  json_transform(
    tbl3.JSON,
    APPEND '$.values' = JSON_QUERY(t2.JS,'$.values'))
  FROM tbl3 
  JOIN (select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3 null on null))) as js
from tbl2)  t2 on JSON_QUERY(tbl3.JSON,'$.keys') = JSON_QUERY(t2.JS,'$.keys')

JSON_TRANSFORM(TBL3.JSON,APPEND'$.VALUES'=JSON_QUERY(T2.JS,'$.VALUES'))
{"keys":["VAL1","VAL2","VAL3"],"values":[["1","2","3"],["a","b","c"],["1","b","3"],[["2","d","f"],["3","b","g"]]]}

fiddle

edited Aug 28, 2023 at 17:49

answered Aug 28, 2023 at 16:30

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

2 Comments

DBox Over a year ago

Thank you for the reply, but I'd like to find a solution assuming I wouldn't have tbl1 anymore. I'm looing for a way to take the existing JSON that was stored in tbl3 and then append the new information from tbl2. @nbk

DBox Over a year ago

I unfortunately need something that will work on 19c and I don't believe json_transform was introduced until 21c. @nbk

Collectives™ on Stack Overflow

Append to JSON_ARRAY in an existing JSON in ORACLE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related