Project Shredder
This series of posts explains different ways to query, resolve and process (and shred) JSON data using Oracle PL/SQL. It covers functions like JSON_VALUE and JSON_DATAGUIDE and prominent PL/SQL features such as Pipelined and Polymorphic Functions and SQL Macros.
Lets dive right into the first post – using JSON_VALUE to validate and populate User Defined Types (UDT).
JSON_VALUE and UDT
The name JSON_VALUE implies a limit of one single JSON value as in this pseudo-code to find the value of the mrn JSON key.
SELECT JSON_VALUE(mrns.'$.mrn)
FROM '{"mrn":99}';
But it can do so much more – especially when combined with UDT’s. They perform validation (do the key names and structures match?) and return PL/SQL objects for native processing. How about some examples?
Here is a simple database table with a JSON column to hold patients Medical Record Number (MRN) values.
CREATE TABLE patient_mrns
( patient_id NUMBER NOT NULL PRIMARY KEY,
pname VARCHAR2(100),
mrns JSON );
And a simple record.
INSERT INTO patient_mrns
VALUES(1,'Rosco','{"MRN":44}');
And a UDT to match by key and structure.
CREATE OR REPLACE TYPE mrn AS OBJECT
( mrn NUMBER );
Now use JSON_VALUE to validate and retrieve the UDT. The magic is in the RETURNING clause.
SELECT mrns,
JSON_VALUE(mrns,'$' RETURNING mrn ) mrn
FROM patient_mrns;
MRNS MRN(MRN)
---------- ----------
{"MRN":44} MRN(44)
What will be returned when the structure and/or keys do not match? Let’s find out with another record in the table.
INSERT INTO patient_mrns
VALUES(2,'Enos','{"MRNVALUE":908}');
And run the query again. The magic is still in the RETURNING clause.
SELECT mrns,
JSON_VALUE(mrns,'$' RETURNING mrn ) mrn
FROM patient_mrns;
MRNS MRN(MRN)
---------------- ---------
{"MRN":44} MRN(44)
{"MRNVALUE":908} MRN(NULL)
If the JSON structure or keys don’t match then a NULL object is returned as shown in the second row.
Let’s create another UDT for mrnvalue.
CREATE OR REPLACE TYPE mrnvalue AS OBJECT
( mrnvalue NUMBER );
And expand the query to use both UDTs.
SELECT mrns,
JSON_VALUE(mrns,'$' RETURNING mrn ) mrn,
JSON_VALUE(mrns,'$' RETURNING mrnvalue ) mrnvalue
FROM patient_mrns;
MRNS MRN(MRN) MRNVALUE(MRNVALUE)
-------------------- ---------- --------------------
{"MRN":44} MRN(44) MRNVALUE(NULL)
{"MRNVALUE":908} MRN(NULL) MRNVALUE(908)
But wait! JSON is supposed to be case specific?! So the JSON key of MRNVALUE should not match the UDT column of mrnvalue? JSON_VALUE is not case specific in this regard so MRNVALUE=mrnvalue.
The JSON structures can be complex as well.
INSERT INTO patient_mrns VALUES(3,'Jesse',
'{"patient" : {"name" : "Jesse",
"mrns": {"os" : "ACME Hospital",
"mrn" : "199"}}}');
This is matched with two UDT’s. One for the os/mrn combination…
CREATE TYPE mrns_t AS OBJECT
(os VARCHAR2(100),
mrn VARCHAR2(30));
…that becomes a subtype for the second UDT for patients.
CREATE TYPE patient_t AS OBJECT
(name VARCHAR2(30),
mrns mrns_t);
And a query.
SELECT JSON_VALUE(mrns,'$.patient' RETURNING patient_t) p
FROM patient_mrns
WHERE JSON_EXISTS(mrns,'$.patient.mrns.mrn');
P(NAME, MRNS(OS, MRN))
--------------------------------------------------
PATIENT_T('Jesse', MRNS_T('ACME Hospital', '199'))
The next post in this series will begin building PLSQL functions around this query.
Thanks for reading.