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!