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: https://dbfiddle.uk/Crq-lzYS — astentx, Aug 30 '23 at 07:01

nbk · Answer 1 · 2023-08-28T17:49:25.797

0

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 '23 at 17:49

answered Aug 28 '23 at 16:30

nbk

45,398
8
30
47

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 Aug 28 '23 at 16:41
Ok, i updated my answer – nbk Aug 28 '23 at 17:49
I unfortunately need something that will work on 19c and I don't believe json_transform was introduced until 21c. @nbk – DBox Aug 28 '23 at 19:30

Append to JSON_ARRAY in an existing JSON in ORACLE

1 Answers1