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 for 2022 (better late than never) on using Oracle SQL Macros to simplify an control data access.
The Business Problem
Patient data and the identifiers such as Medical Record Numbers (MRN’s) and Enterprise Master Patient Indexes (EMPI) seem so simple on the surface. Each unique patient at a hospital gets assigned a unique MRN and an EMPI to identify them across hospitals.
Seems simple enough.
It isn’t. Here’s an example of what can happen:
- John Doe gets a Chest X-Ray
- An MRN of R1 is assigned by the Radiology system
- An EMPI of E1 is assigned
- He gets a Cardio Stress Test
- An MRN of C1 is assigned by the Cardiology system
- An EMPI of E2 is assigned
- He gets an eye exam
- An MRN is assigned by the Ophthalmology system
- An EMPI of E3 is assigned
If all of the systems were up and communicating correctly the only EMPI assigned would have been E1.
Situations like this occur often enough to make users doubt the completeness of the patients history and the system itself.
The Technical Problem
Java Script Object Notation (JSON) simplifies storage of data. For example one patient record may have two EMPI’s assigned – no problem in JSON. Some MRN’s need to be deleted while others need to be flagged as inactive – no problem. Sometimes JSON links need to be created that the application is not even aware of – no problem with JSON because its just text with keys and values. Some systems record the MRN and EMPI values in different locations in JSON. Also no problem!
But…
It quickly gets complicated (and slow) to access the data via SQL queries that include (or miss altogether) any of the complexity.
The Data
One JSON Table
Yes – just one lonely table and with an equally lonely column.
CREATE TABLE json_patient ( patientdata JSON );
Because its a JSON column the structure is determined by each row stored within. The Oracle Data Dictionary does not known anything about the data structure beyond the table and column name.
DESCRIBE json_patient
Name Null? Type
----------- ----- -----
PATIENTDATA JSON
SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'JSON_PATIENT';
COLUMN_NAME DATA_TYPE
----------- ----------
PATIENTDATA JSON
The key locations can vary as long as it is valid JSON.
BEGIN
FOR counter IN 1..1
INSERT INTO json_patient VALUES('{"pname":"Rosco Coltrane' ||
counter || '","EMPI":' || counter || '}');
INSERT INTO json_patient VALUES('{"pname":"Enos Strate' ||
counter || '","mrns":{EMPI:' || counter || '}}');
INSERT INTO json_patient VALUES('{"pname":"Boss Hogg' || counter || '"}');
END LOOP;
END;
The structure of the JSON is visible via the JSON_DATAGUIDE function like this.
SELECT JSON_DATAGUIDE(patientdata,
DBMS_JSON.FORMAT_FLAT,
DBMS_JSON.PRETTY) dg
FROM json_patient;
DG
--------------------------------------------
[
{
"o:path" : "$",
"type" : "object",
"o:length" : 1
},
{
"o:path" : "$.EMPI",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.mrns",
"type" : "object",
"o:length" : 1
},
{
"o:path" : "$.mrns.EMPI",
"type" : "number",
"o:length" : 2
},
{
"o:path" : "$.pname",
"type" : "string",
"o:length" : 16
}
]
The First Problem
The data inserted does not follow a consistent format – the EMPI key occurs in two places (so far) new data may store it in others.
The Solution (Part 1)
EMPI Finder Function
I need a quick way to find the EMPI key. I’ll use PL/SQL with JSON_DATAGUIDE to parse the JSON and find the EMPI key. Here’s the function:
CREATE OR REPLACE FUNCTION find_empi ( p_json JSON )
RETURN VARCHAR2
DETERMINISTIC IS
CURSOR jcurs ( cp_json JSON ) IS
WITH dg_table AS ( SELECT JSON_DATAGUIDE(cp_json) dg_doc
FROM DUAL )
SELECT guide_path.path
FROM dg_table,
JSON_TABLE(dg_doc, '$[*]'
COLUMNS
path VARCHAR2(40) PATH '$."o:path"') guide_path;
v_jpath VARCHAR2(40);
v_ret_val VARCHAR2(100) := 'NO EMPI';
BEGIN
OPEN jcurs(p_json);
LOOP
FETCH jcurs INTO v_jpath;
IF v_jpath LIKE '%.EMPI' THEN
v_ret_val := v_jpath;
EXIT;
END IF;
EXIT WHEN jcurs%NOTFOUND;
END LOOP;
CLOSE jcurs;
RETURN(v_ret_val);
END;
The Table Macro
And next I code an SQL Macro. It simply defines the text that Oracle parses into a query. Note that it determines the name of the column (empi) returned.
CREATE FUNCTION add_empi
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
RETURN q'{SELECT jp.*,
find_empi(patientdata) empi
FROM json_patient jp}';
END;
Using The Table Macro
Now I can query the function to find the EMPI keys easily. And the column name from the Macro can be used as criteria in the where clause!
SELECT *
FROM TABLE(add_empi)
WHERE empi = '$.EMPI';
PATIENTDATA EMPI
---------------------------------------- ----------
{"pname":"Rosco Coltrane1","EMPI":1} $.EMPI
What if no rows have that value?
SELECT *
FROM TABLE(add_empi)
WHERE empi = '$.NOEMPI';
no rows selected
Only columns defined in the SQL Macro can be used in queries.
SELECT *
FROM TABLE(add_empi)
WHERE not_there = 11;
WHERE not_there = 11
*
ERROR at line 3:
ORA-00904: "NOT_THERE": invalid identifier
The rest of the posts in this series will explore SQL Macros and discuss ways design, monitor, and optimize them.
Thanks for reading!