Dynamically fetch columns as JSON in oracle

You may achieve this by using Polymorphic Table Function available since 18c.

Define the function that will project only specific columns and serialize others into JSON. An implementation is below.

1. PTF package (function implementation).

create package pkg_jsonify as
  /*Package to implement PTF.
  Functions below implement the API
  described in the DBMS_TF package*/

  function describe(
    tab in out dbms_tf.table_t,
    keep_cols in dbms_tf.columns_t
  ) return dbms_tf.describe_t
  ;
  
  procedure fetch_rows;
end pkg_jsonify;
/

create package body pkg_jsonify as

  function describe(
    tab in out dbms_tf.table_t,
    keep_cols in dbms_tf.columns_t
  ) return dbms_tf.describe_t
  as
    add_cols dbms_tf.columns_new_t;
    new_col_cnt pls_integer := 0;
  begin
    for i in 1..tab.column.count loop
      /*Initially remove column from the output*/
      tab.column(i).pass_through := FALSE;
      /*and keep it in the row processing (to be available for serialization*/
      tab.column(i).for_read := TRUE;
      for j in 1..keep_cols.count loop
        /*If column is in a projection list, then remove it
        from processing and pass it as is*/
        if tab.column(i).description.name = keep_cols(j)
          then
            tab.column(i).pass_through := TRUE;
            /*Skip column in the row processing (JSON serialization)*/
            tab.column(i).for_read := FALSE;
        end if;
      end loop;
    end loop;

    /*Define new output column*/
    add_cols := dbms_tf.columns_new_t(
      1 => dbms_tf.column_metadata_t(
        name => 'JSON_DOC_DATA',
        type => dbms_tf.type_clob
      )
    );
    
    /*Return the list of new cols*/
    return dbms_tf.describe_t(
      new_columns => add_cols
    );
  end;
  
  procedure fetch_rows
  /*Process rowset and serialize cols*/
  as
    rowset dbms_tf.row_set_t;
    num_rows pls_integer;
    new_col dbms_tf.tab_clob_t;
  begin
    /*Get rows*/
    dbms_tf.get_row_set(
      rowset => rowset,
      row_count => num_rows
    );
    
    for rn in 1..num_rows loop
      /*Calculate new column value in the same row*/
      new_col(rn) := dbms_tf.row_to_char(
        rowset => rowset,
        rid => rn,
        format => dbms_tf.FORMAT_JSON
      );
    end loop;
    
    /*Put column to output*/
    dbms_tf.put_col(
      columnid => 1,
      collection => new_col
    );

  end;
end pkg_jsonify;
/

2. PTF function definition based on the package.

create function f_cols_to_json(tab in table, cols in columns)
/*Function to serialize into JSON using PTF*/
return table pipelined
row polymorphic using pkg_jsonify;
/

3. Demo.

create table sample_tab
as
select
  trunc(level/10) as id
  , mod(level, 3) as id2
  , level as val1
  , level * level as val2
from dual
connect by level < 40

with prep as (
  select
    id
    , id2
    , sum(val1) as val1_sum
    , max(val2) as val2_max
  from sample_tab
  
  group by
    id
    , id2
)
select *
from table(f_cols_to_json(prep, columns(id, id2)))

with prep as (
  select
    id
    , id2
    , sum(val1) as val1_sum
    , max(val2) as val2_max
  from sample_tab
  group by
    id
    , id2
)
select *
from table(f_cols_to_json(prep, columns(id)))

fiddle