2020 – Handling Numbers

To get things up and running the original code for Project 2020 processed string JSON values only. But that is not much use if one has numeric values in their JSON. So I have updated the Polymorphic Table Function (PTF) to handle numbers.

The changes required were very arduous and involved 17 million hours of coding and testing…actually it was easy. Here are the changes:

Add a number column to the result set structure:

WHEN 'number' THEN v_type := DBMS_TF.TYPE_NUMBER;
  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;

After extracting data with JSON_VALUE calls we need to check the datatype and add to the appropriate PTF table.

IF v_put_cols(put_col_counter).type = 1 THEN
v_row_set_out(put_col_counter).tab_varchar2(row_counter)
  := v_ret_val;
ELSIF v_put_cols(put_col_counter).type = 2 THEN
  v_row_set_out(put_col_counter).tab_number(row_counter)
    := v_ret_val;
END IF;

That’s it!

The code is here

Thanks for reading!

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.

JSON_VALUE(json_data,'$.KEY5')
JSON_VALUE(json_data,'$.OBJECT5.KEY5')

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;
  ELSE
    NULL;
END CASE;

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

Thanks for reading!