implestrat.com

This post finally puts queried data into columns in Polymorphic Table Function (PTF) result set. The algorithm is fairly straightforward:

• Get a set of rows (aka ROWSET) using DBMS_TF.GET_ROW_SET
• Find the column in the ROWSET that contains JSON
  • For each row in the ROWSET
    • Grab the JSON data
      • For each KEY we care about
        • Use JSON_VALUE to politely ask the JSON if it contains the KEY
        • If it does then grab the value and put it in the matching column we have added to the rowset
        • If it doesn’t then no hard feelings – just carry on

Think of it as a game of fish using JSON paths.

Q: Do you have $.KEY1?
A: No. Here is a NULL value instead
Q: Do you have $.KEY2?
A: Yes. Here is it’s value

Here is the code…

-- for every row in the row set...
FOR row_counter IN 1..v_rowcount LOOP 

v_chr_t_counter := 1; 

DECLARE 
  v_whole_json CLOB; 
BEGIN 
  -- 
  -- Get the contents of: 
  -- 1) The current row (row_counter) of... 
  -- 2) The table of CLOB alues (tab_clob) for the... 
  -- 3) Current column (col_counter) in the... 
  -- 4) Fetched row set (v_row_set) 
  -- 
  v_whole_json := 
         v_row_set(col_counter).tab_clob(row_counter); 
  -- 
  -- for every PUT column...
  FOR put_col_counter IN 1..v_put_cols.COUNT LOOP 
    FOR counter2 IN 1..3 LOOP 
      IF counter2 = 2 THEN 
        -- 
        -- Create a JSON_VALUE call like this 
        -- JSON_VALUE(whole JSON, current JSON path ) 
        -- eg: JSON_VALUE('{"KEY1":"VALUE1"}','$.KEY1'); 
        -- JSON does not fail if the path does not exist
        -- 
        DECLARE 
          v_sql VARCHAR2(1000); 
          v_json VARCHAR2(1000) := v_whole_json;
          v_attr VARCHAR2(1000) := v_chr_t(v_chr_t_counter); 
          v_ret_val VARCHAR2(1000); 
        BEGIN 
          v_sql := 'DECLARE q VARCHAR2(1000); 
                    BEGIN q := JSON_VALUE(:1,:2); :3 := q;
                    END;'; 
          EXECUTE IMMEDIATE v_sql USING v_json,
                            v_attr, OUT v_ret_val; 
          -- 
          -- Slide the retrieved value into the... 
          -- 1) Current row (row_counter) of the... 
          -- 2) Table of VARCHAR2 values (tab_varchar2) of the... 
          -- 3) Current put column (counter) 
          -- 
  v_row_set_out(put_col_counter).tab_varchar2(row_counter) 
       := v_ret_val; 
        END; 
      END IF; 
      v_chr_t_counter := v_chr_t_counter + 1;
    END LOOP;
  END LOOP; -- every PUT col 
END;

END LOOP; -- every row of data

Then at the end attach the assembled PUT row set to the fetched GET row set.

DBMS_TF.PUT_ROW_SET(v_row_set_out);

The final code for this whole effort is posted on the Oracle Live SQL site here

Thanks for reading!

It’s finally time to get data to populate the structures defined in previous posts. The code for this is pretty straightforward.

• Fetch a set of rows
• Navigate through the row set
  • column by column
  • row by row
    • extracting data

First up – some variables…

v_row_set DBMS_TF.ROW_SET_T; -- fetched rows go here
v_clob    CLOB;              -- JSON CLOB goes here
v_number  NUMBER;            -- Numeric values go here

And fetch some rows.

-- Get a set of rows
DBMS_TF.GET_ROW_SET( rowset    => v_row_set,
                     row_count => v_rowcount,
                     col_count => v_colcount);

Now lúbach through the fetched result set and display values.

-- for every GET column in row set...
FOR col_counter IN 1..v_colcount LOOP

  -- for every row in row set...
  FOR row_counter IN 1..v_rowcount LOOP

   DBMS_OUTPUT.PUT_LINE('Row ' || row_counter);
   DBMS_OUTPUT.PUT(v_row_set(col_counter).description.name ||
                     ' ');
   DBMS_OUTPUT.PUT(DBMS_TF.COLUMN_TYPE_NAME(
             v_row_set(col_counter).description) || ' ');

   -- get data from the appropriate TABLE based on the
   -- datatype of the column
   CASE DBMS_TF.COLUMN_TYPE_NAME(
                  v_row_set(col_counter).description)
     WHEN 'NUMBER' THEN
       v_number := 
          v_row_set(col_counter).tab_number(row_counter);
     WHEN 'CLOB' THEN
       v_clob := 
          v_row_set(col_counter).tab_clob(row_counter);
     END CASE;

     -- output the value based on the datatype of the column
     CASE DBMS_TF.COLUMN_TYPE_NAME(
                    v_row_set(col_counter).description)
       WHEN 'NUMBER' THEN
         DBMS_OUTPUT.PUT_LINE(v_number);
       WHEN 'CLOB' THEN
         DBMS_OUTPUT.PUT_LINE(v_clob);
     END CASE;

   END LOOP; -- every row

END LOOP; -- every GET column

And the torthaí…

Row 1
"JSON_ID" NUMBER 1
Row 2
"JSON_ID" NUMBER 2
Row 1
"JSON_DATA" CLOB {"KEY1":"VALUE1"}
Row 2
"JSON_DATA" CLOB {"KEY2":"VALUE2","KEY3":"VALUE3"}

The next post puts data and structure together!

Thanks for reading!

This post recaps all of the pieces we have to put our JSON data into colums in rows. We have the queried (aka GET) columns, the added (aka PUT) columns and the JSON paths.

Here are the declared variables that store all of that information. They are are populated in the DESCRIBE function and then accessed in the FETCH_ROWS procedure as follows:

-- the JSON key name, datatype and path are stored
-- in V_CHR_T - these will eventually be used in
-- JSON_VALUE calls 
v_chr_t DBMS_TF.CSTORE_CHR_T;

-- V_ENV_T will containe the environment of each
-- FETCH_ROWS execution including information
-- about the GET and PUT columns
v_env_t DBMS_TF.env_t;

-- Details such as column name will be stored in
-- V_PUTS and V_GETS
v_puts  DBMS_TF.TABLE_METADATA_T;
v_gets  DBMS_TF.TABLE_METADATA_T;

And here is how we get the environment information.

v_env_t := DBMS_TF.GET_ENV;

And output the colum names for the queried – aka GET columns.

v_gets := v_env_t.get_columns;
FOR counter IN 1..v_gets.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('GET ' || counter || ' ' || 
                       v_gets(counter).name);
END LOOP;

Then do the same for the added – aka PUT – columns.

v_puts := v_env_t.put_columns;
FOR counter IN 1..v_puts.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('PUT ' || counter || ' ' || 
                       v_puts(counter).name);
END LOOP;

And last but not least we output the JSON key name, datatype and path.

DBMS_TF.CSTORE_GET(v_chr_t);
FOR counter IN 1..v_chr_t.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('CHR ' || counter || ' = ' ||
                       v_chr_t(counter));
END LOOP;

And now for the output.

First up are the columns that came straight from the queried table.

Get 1 "JSON_ID"
Get 2 "JSON_DATA"

And now the columns that we added. They will evenetually hold values extracted from the JSON column named JSON_DATA.

Put 1 "KEY1"
Put 2 "KEY2"
Put 3 "KEY3"
Put 4 "KEY4"

And last but not least – information about the JSON data itself. At this point we will focus on the path values that start with $.

CHR 1 = KEY1
CHR 2 = $.KEY1
CHR 3 = string
CHR 4 = KEY2
CHR 5 = $.KEY2
CHR 6 = string
CHR 7 = KEY3
CHR 8 = $.KEY3
CHR 9 = string
CHR 10 = KEY4
CHR 11 = $.KEY4
CHR 12 = string

We now have all of the prieces we need to complete the result set. If I was to code it using my Imaginary-Conversational-Language (ICL) it would look like this:

1. I have a rows and columns queried here.
2. Let me check the enironment. Look’s like it’s KEY1.
3. Alright. How do I find that in the JSON?
4. Let me check the CSTORE values. Looks like $.KEY1 will get you there.
5. Sweet. Hey JSON_VALUE. Get me $.KEY1
6. Here you go!
7. Thanks! “VALUE1” – that’s it? Thats some very boring JSON If you ask me.
8. We didn’t ask.
9. OK, dont judge me! Whats the next PUT column?

The code for this post is available here

The next post will start putting it together to get and display the JSON values.

Thanks for reading!