2021 – JSON Plus Database Objects/Types = Flexibility

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 is the gain?

Thankfully I am discovering more flexibility using Oracle Objects (aka Types) for JSON data. Allow me to demonstrate…

Here are two rows of JSON data for testing.

SELECT *
  FROM json_patient;
PATIENT_DATA
--------------------------------------------------------------------------------
{"pname" : "Patient 1","mrns" : [{"OS" : 1,"MRN" : "1"},{"OS" : 2,"MRN" : "2"}]}
{"pname" : "Patient 2","mrns" : [{"OS" : 1,"MRN" : "3"},{"OS" : 2,"MRN" : "4"}]}

I’ll create a database object (or type) named MRN_O with the same structure as the entries in the mrns array.

CREATE OR REPLACE TYPE mrn_o AS OBJECT ( os   NUMBER,
                                          mrn VARCHAR2(100) );

Then I’ll use JSON_VALUE to query directly into the object and display the values found.

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]' RETURNING mrn_o ) mrn_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_o.os || ' ' || x.mrn_o.mrn);
  END LOOP;
END;

MRN 1 1
MRN 1 3

No need to declare specific singular variables for each piece of JSON data! I like that!

But what if the structures differ? I’ll find out by adding two columns to the object.

CREATE OR REPLACE TYPE mrn_date_o AS OBJECT ( os     NUMBER,
                                               mrn   VARCHAR2(100),
                                               sdate DATE,
                                               edate DATE);

How does JSON_VALUE do with the new object?

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]' RETURNING mrn_date_o ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

MRN 1 1
DT
MRN 1 3
DT

The query did not fail and the non-matching elements were set to NULL. That’s pretty flexible! But what if I really want it to fail? That’s done with the ERROR ON MISMATCH clause.

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]' 
                    RETURNING mrn_date_o 
                    ERROR ON MISMATCH ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

Raises ORA-40601: missing data for object type conversion ORA-06512

Another option for mismatched data is to set all values to NULL if there is a mismatch.

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
                    RETURNING mrn_date_o 
                    NULL ON MISMATCH ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

MRN
DT
MRN
DT

All values are set to NULL.

The next few posts will dig deeper into using Objects with JSON.

Thanks for reading!