Dynamic Column List in JSON Query in Oracle

Question

I'm looking for a way to create/pass a dynamic column list for use in generating a JSON document.

Creating the following (on Oracle 19c):

CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
INSERT INTO tbl1 VALUES ('1','2','3');
INSERT INTO tbl1 VALUES ('a','b','c');
INSERT INTO tbl1 VALUES ('1','b','3');

This query gives me the output I want, but is static in regards to the column names (or val1, val2, val3) in the keys and values sections.

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

I'm looking for a way to replace the keys and values portion with a dynamic list. This isn't exactly right (still has static value calls and is surrounded by extra "s on each end for the keys, but is as close as I have been able to achieve. Looking for help in making this fully dynamic. Ideally would be able to change the table in the with clause and build for other tables as well.

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

fiddle

MT0 · Accepted Answer · 2023-08-07T09:08:27.477

You can create a helper function:

CREATE FUNCTION object_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(js.get_string(keys(i)));
  END LOOP;
  RETURN arr.to_string();
END;
/

Then you can use:

SELECT json_object(
         KEY 'keys' VALUE MIN(p.keys) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(object_to_array(t.json) FORMAT JSON)
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1) t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.json) AS data FROM DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(SUBSTR(path, 3)) AS keys
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(20) PATH '$."o:path"'
                  )
                )
         WHERE  path != '$'
       ) p

Which, for the sample data:

CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));

INSERT INTO tbl1 VALUES ('1','2','3');
INSERT INTO tbl1 VALUES ('a','b','c');
INSERT INTO tbl1 VALUES ('1','b','3');
INSERT INTO tbl1 VALUES ('2','d',NULL);

Outputs:

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

Oracle 21 fiddle

Or, you can create two helper functions:

CREATE FUNCTION object_keys_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(keys(i));
  END LOOP;
  RETURN arr.to_string();
END;
/

CREATE FUNCTION object_values_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(js.get_string(keys(i)));
  END LOOP;
  RETURN arr.to_string();
END;
/

Then you can use:

SELECT json_object(
         KEY 'keys' VALUE MIN(object_keys_to_array(json)) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(object_values_to_array(json) FORMAT JSON)
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1)

Which outputs the same.

Oracle 21 fiddle

@Astentx commented to suggest a method without a helper function:

SELECT JSON_OBJECT(
         KEY 'keys' VALUE MIN(p.keys) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(
           JSON_QUERY(
             t.json FORMAT JSON,
             '$.*'
             WITH UNCONDITIONAL ARRAY WRAPPER
           ) FORMAT JSON
         )
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1) t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.json) AS data FROM DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(SUBSTR(path, 3)) AS keys
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(20) PATH '$."o:path"'
                  )
                )
         WHERE  path != '$'
       ) p

Which also outputs the same.

Oracle 21 fiddle

Thanks so much. I was trying to find a way with just SQL but wasn't thinking it was really possible. I had thought of execute immediate implementations but this is much nicer. Obviously new to JSON in Oracle, this a big help. @MT0 — DBox, Aug 06 '23 at 22:01
@MT0 It's possible to transform object to array without helper function using `json_query` and `$.*` as path literal: `json_query(t.json format json, '$.*' with unconditional array wrapper`. Based on your fiddle: https://dbfiddle.uk/QFAgNKaR — astentx, Aug 07 '23 at 07:22

Dynamic Column List in JSON Query in Oracle

1 Answers1