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

Answer 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