the-json-expedition

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.