2020 – Handling JSON Objects

So far the 2020 project code only handles string values such asĀ  {“KEY1″:”VALUE1”} which becomes a column named KEY1 with a value of VALUE1. That’s fine for introductory purposes but what about JSON objects such as {“OBJECT5”:{“KEY5″:”VALUE5”}}?

Thankfully the combination of JSON_DATA_GUIDE, JSON_VALUE and Polymorphic Table Functions (PTF’s) handle’s it easily.

First up is the Data Guide with 2 entries for OBJECT5.

path=$.OBJECT5      type=object length=32
path=$.OBJECT5.KEY5 type=string length=8

Thus the JSON_VALUE calls the 2020 PTF generates will be.


The first will fail silently because that is the default ON ERROR behavior for JSON_VALUE. The second call will successfully find VALUE5 and put it into a column name OBJECT5KEY5.

The PTF code needs a tweak to ignore objects for now. That is done with a CASE statement.

CASE x.type
  WHEN 'string' THEN v_type := DBMS_TF.TYPE_VARCHAR2;
     v_new_cols(v_col_id) := 
       DBMS_TF.COLUMN_METADATA_T( type => v_type,
                                  name => v_colname,
                                  max_len => x.length );
     v_col_info_id := v_col_info_id + 1;
     v_chr_t(v_col_info_id) := v_colname;
     v_col_info_id := v_col_info_id + 1;
     v_chr_t(v_col_info_id) := x.path;
     v_col_info_id := v_col_info_id + 1;
     v_chr_t(v_col_info_id) := x.type;

The working code is available on the Oracle Live SQL siteĀ here

Thanks for reading!

Leave a Comment