Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
1 rows affected

INSERT INTO tbl1 VALUES ('a','b','c');
1 rows affected

INSERT INTO tbl1 VALUES ('1','b','3');
1 rows affected

INSERT INTO tbl2 VALUES ('2','d','f');
1 rows affected

INSERT INTO tbl2 VALUES ('3','b','g');
1 rows affected

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


JS


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


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
1 rows affected

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"]]}


{"keys":["VAL1","VAL2","VAL3"],"values":[["1","2","3"],["a","b","c"],["1","b","3"],["2","d","f"],["3","b","g"]]}
ORA-00900: invalid SQL statement

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"]]]}