Oracle 23c

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));
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

SELECT JSON_ARRAYAGG(JSON_OBJECT (*) returning varchar2) AS JSON_VAR
FROM tbl1;


JSON_VAR


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


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


JS


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


with qry as (
  select tbl1.*, 
  (select
  listagg ('''''' || column_name || '''''', ',') within group (order by column_name)
  from all_tab_cols
  where table_name = 'TBL1') as cols
  from tbl1
)
select json_object(
'keys' : [json_query(json_arrayagg(qry.cols), '$[0]') FORMAT JSON],
'values' : json_arrayagg(json_array(val1, val2, val3 null on null))) as js
from qry


JS


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