Quick JSON_TABLE Generator View

quick_json_table_vw.sql

create or replace view quick_json_table_vw as
--This query parses the file profile (from apex_data_parser.get_file_profile) and generates json_table query
--The file profile will be based on the last executed call to apex_data_parser.parse,
--so this should be ran after running the parse function for a new row selector or document
select
   row_selector,
   row_selector_alias,
   json_table_sql,
   type_record,
   parse_function_spec||';' as parse_function_spec,
   to_clob(
      parse_function_spec||chr(10)||
      apex_string.format(
         q'!   is
           !   begin
           !      for json_row in (
           !   %3
           !      ) loop
           !         pipe row(json_row);
           !      end loop;
           !      return;
           !   end parse_%1;!',
         p1 => row_selector_alias,
         p2 => table_type_name,
         p3 => json_table_sql,
         p_max_length => 4000,
         p_prefix => '!'
      )
   )parse_function_body
from (
   select
      row_selector,
      --json_table query
      row_selector_alias,
      record_type_name,
      table_type_name,
      to_clob(
         '   select jt.* from '||chr(10)||
         '      json_table(p_json, ''$.'||row_selector||'[*]'' '||chr(10)||
         '      columns('||chr(10)||
         listagg(leading_comma||column_name||data_type||json_path, chr(10) ) ||chr(10)||
         '         )'||chr(10)||
         '      ) jt' --use table alias "jt" for easy selection
      ) as json_table_sql,
      --type record for package
      --todo: only compute record type name once
      to_clob(
         '   type '||record_type_name||' is record ('||chr(10)
         ||listagg(leading_comma||column_name||data_type, chr(10) ) ||chr(10)
         ||'   );'||chr(10)||chr(10)
         ||'   type '||table_type_name||' is table of '||record_type_name||';'
      ) as type_record,
      to_clob(
         apex_string.format(
            q'!   function parse_%1 (
              !      p_json in blob
              !   ) return %2 pipelined!',
            p1 => row_selector_alias,
            p2 => table_type_name,
            p_max_length => 4000,
            p_prefix => '!'
         )
      ) parse_function_spec
   from (
      --begin inner query, run this alone to view row output for each column
      with file_profile as (
         --run this query to see mostly raw output from parsing the data profile returned by apex_data_parser.get_file_profile
         select
            file_type --4: json
          , row_selector
          --turns camelCase to camel_case, not all json docs have this tho
          --TODO: figure out how to only select capital letters immediately preceded by lowercase
          , lower(
               replace(
                  replace(regexp_replace(row_selector, '([A-Z])', '_\1', 2), '.' ,'_')
                , '__'
                , '_'
               ) --remove double-underscore from incorrectly parsed camel-case
            ) row_selector_alias
          , column_name
          --turns camelCase to camel_case, not all json docs have this tho
          , lower(
               replace(
                  replace(regexp_replace(selector, '([A-Z])', '_\1', 2), '.' ,'_')
                , '__'
                , '_'
               ) --remove double-underscore from incorrectly parsed camel-case
            ) column_alias
          , data_type
          , data_type_len
          , decimal_char
          , selector
          , is_json
         from
            json_table( apex_data_parser.get_file_profile format json, '$'
               columns(
                  --document properties
                  file_type     number          path '$."file-type"'
                , row_selector  varchar2(4000)  path '$."row-selector"'
                , nested path '$.columns[*]'
                     --document columns
                     columns(
                        column_name    varchar2(128 char) path '$.name'
                      , data_type      number             path '$."data-type"'
                      , data_type_len  number             path '$."data-type-len"'
                      , decimal_char   varchar2(2 char)   path '$."decimal-char"'
                      , selector       varchar2(256 char) path '$.selector'
                      , is_json        varchar2(5 char)   path '$."is-json"'
                     )
                  )
            ) --jt
         order by selector --this order by also determines where the leading comma is placed
      )
      select
         row_selector 
       , row_selector_alias
       , 'r_'||row_selector_alias||'_type' as record_type_name
       , 't_'||row_selector_alias||'_type' as table_type_name
       , case rownum
            when 1 then 
               '         '
            else
               '       , ' --leading_comma
         end leading_comma
         -- cast to char(length) will right pad spaces so we can align columns
         --swap with column_alias or column_name 
       , cast(lower(column_alias) as char(42)) column_name
       , cast(
            case data_type
               when 1 then
                  'varchar2('||nvl(data_type_len, 4000)||' char)'
               when 2 then
                  'number'
               when 3 then
                  'date'
               when 5 then
                  'timestamp(6) with time zone'
               when 11 then
                  'varchar2(4000) format json'
               else
                  to_char(data_type)
            end
            as char(32)
         ) data_type
       , 'path ''$.'||selector||'''' json_path
       , data_type_len
       , decimal_char
       , selector
       , is_json
      from
         file_profile
      --end inner query
   )
   group by row_selector, row_selector_alias, record_type_name, table_type_name
)
;
/