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:
- I have a rows and columns queried here.
- Let me check the enironment. Look’s like it’s KEY1.
- Alright. How do I find that in the JSON?
- Let me check the CSTORE values. Looks like $.KEY1 will get you there.
- Sweet. Hey JSON_VALUE. Get me $.KEY1
- Here you go!
- Thanks! “VALUE1” – that’s it? Thats some very boring JSON If you ask me.
- We didn’t ask.
- 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!