Shredder 1 – JSON_VALUE and UDT

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.

SQL Macros Part 2

Not content to just put EMPI keys and values in disparate places in the JSON some applications started entering EMPI as objects. INSERT INTO json_patient VALUES(‘{“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}}’); My table function tried its best… SELECT * FROM TABLE(add_empi) WHERE empi = ‘$.EMPI’; PATIENTDATA EMPI ———————————————- ——————————— {“pname”:”Rosco Coltrane1″,”EMPI”:1} $.EMPI {“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}} $.EMPI It did indeed find EMPI but … Read more

2022 – SQL Macros Introduction

My first JSON DB project did not go well because the data structure (or lack thereof) quickly overwhelmed the applications ability to process it. If I had it to do over again I would have put more of the application in the database to simplify and manage access. That experience inspires this series of posts … Read more

2022 – JSON Search Index Synchronization

Another option for synchronizing JSON Search Indexes is Manual which is enabled by altering the meta-data of the index like this: ALTER INDEX jindex PARAMETERS (‘replace metadata sync (manual)’); Now when DML occurs… INSERT INTO jtable VALUES(‘{“JTOP”:3}’); SELECT * FROM jtable WHERE JSON_EXISTS(jcol,’$.JTOP’); JCOL ——————- {“JTOP”:1} {“JTOP”:2} It will not show up in queries (even … Read more

2021 – Complex Structure Changes 1 – JSON

Its time to deal with the inevitable complex changes to the database. I’ll start working through the JSON changes  with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more

2021 – Complex Structure Changes 1 – RDBMS

Its time to deal with the inevitable complex changes to the database. I’ll start working through the RDBMS changes  with this post that handles the following: Non-Unique Patient Names Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management … Read more

2021 – Large Data Structure Changes

Previous posts comparing JSON and RDBMS structure changes used a simple example adding RDBMS date columns or JSON keys. Now its time for something more challenging: Enterprise Master Patient Index (EMPI) All Patients must be given a new master identifier encompassing one or more patient records and their associated Medical Record Numbers (MRN’s) If the … Read more

2021 – Simply Evolving JSON Structure (Updates)

JSON_TRANSFORM can ensure JSON structure when records are inserted into the database using an INSTEAD OF INSERT trigger on a view. CREATE OR REPLACE TRIGGER instead_patient INSTEAD OF INSERT ON migrated_patient BEGIN INSERT INTO json_patient VALUES ( JSON_TRANSFORM(:new.migrated, SET ‘$.unique_os’ = OS_LIST_UNIQUE( JSON_QUERY(:new.migrated,’$.mrns[*].OS’ WITH WRAPPER) ) REPLACE ON EXISTING, INSERT ‘$.mrns[*].sdate’ = SYSDATE IGNORE ON … Read more