the-json-expedition

The simple explanation of the JSON_TRANSFORM SET operation is that it sets values for specified JSON keys. For example “set $.KEY1 to 42′ will set the value of the KEY1 to 42. That’s pretty straightforward. The real power is the additional behaviours that can be defined such as: If $.KEY1 is missing then add it … Read more

A previous post in this series dealt with changes to the JSON data structures. Specifically the addition and population of two new key-value pairs. Two other posts here and here showed how object types can be used to make queries simpler as structures change. This is powerful but also quite limited. What we really need … Read more

Now that I know how flexible selecting JSON and object types are I am wondering if I can execute code in the object type body as well. I’ll try it now with this fully declared object type. CREATE OR REPLACE TYPE mrn_date_o AS OBJECT ( os NUMBER, mrn VARCHAR2(100), CONSTRUCTOR FUNCTION mrn_date_o ( SELF IN … Read more

The previous post introduced the ON MISMATCH for loading mismatched data into an Oracle Object. This post expands on that by explaining the three specific types of mismatches. They are: MISSING DATA The JSON does not have all of the keys to match the object type EXTRA DATA The JSON has more keys than the … Read more

My experience with JSON and SQL and PLSQL has left me wanting more flexibility. I naively though that the flexible nature of JSON data would be matched by equally flexible SQL and PLSQL options. Thus I grew frustrated having to retool code to handle data structure changes just like I do with Relational Databases. Where … Read more

The last modification for the JSON structure change is the PL/SQL API. First up lets make sure the query API works. BEGIN DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname( p_os => 1, p_mrn => ‘1’)); END; Patient 1 That is the correct result. Now for the add_patient procedure. BEGIN jpatient_pkg.add_patient( p_name => ‘Bob’, p_os => 1, p_mrn => 11 ); END; … Read more

The patient management system has been given some new requirements. OS/MRN combinations must have a start date The start date defaults to the current date OS/MRN combinations may have and end date In the RDBMS world this is done in 3 steps: Add New Columns Set New Column Values Set NULL In JSON its done … Read more

The patient management system has been given some new requirements. OS/MRN combinations must have a start date The start date defaults to the current date OS/MRN combinations may have and end date And there is no requirement for the database to be available while the changes are made! That makes things really simple… Step 1 … Read more

This post compares index creation and usage for RDBMS data and JSON data. I’ll focus on three specific aspects. Creating Indexes Including JSON Search Indexes Verifying Index Usage Oracle’s EXPLAIN PLAN utility shows if an index was used or not in a query Resolving Index Usage Oracle’s EXPLAIN PLAN utility shows the actual actual criteria … Read more

Iudith Mentzel noticed a problem in my OS_LIST_UNIQUE function. It relied on the Ordering System (OS) number being incremental. For example [3,2,1] would not be considered a unique list. The problem lay in the following steps: Load the passed JSON Array into a table 1 Loop through table 1 sequentially to load unique values into … Read more