Way back in the previous decade I asked some PL/SQL developers how they might use Polymorphic Table Functions (PTF) and JSON Dataguides. Almost all of the replies involved translating JSON keys and values into columns and rows on the fly. So with that and a brand new decade in mind I’m embarking on a new series called the 2020 Project.
In a nutshell I’ll be building a PTF to go from this JSON data stored in an Oracle DB.
SELECT *
FROM json_stuff;
JSON_ID JSON_DATA
------- ---------------------------------
1 {"KEY1":"VALUE1"}
2 {"KEY2":"VALUE2","KEY3":"VALUE3"}
To displaying the keys and values as columns and values like this:
SELECT key1,
key2,
key3
FROM add_dataguide_func(json_stuff);
KEY1 KEY2 KEY3
------ ------ ------
VALUE1
VALUE2 VALUE3
Over the next few months I’ll assemble and explain code to perform the translation. I’ll post the code on the Oracle livesql site so you can follow along. The introduction code snippets are available here.
NB1 If you just need to qucikly get translations done then have a look a Oracle’s Dataguide indexes and views created by the DBMS_JSON package.
NB2 If you are looking for an in depth tour of PTF’s and JSON Dataguides then I think you’ll enjoy this series.
Thanks for reading!