2022 – SQL Macros Introduction

by

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:

  1. John Doe gets a Chest X-Ray
    1. An MRN of R1 is assigned by the Radiology system
    2. An EMPI of E1 is assigned
  2. He gets a Cardio Stress Test
    1. An MRN of C1 is assigned by the Cardiology system
    2. An EMPI of E2 is assigned
  3. He gets an eye exam
    1. An MRN is assigned by the Ophthalmology system
    2. 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!

Leave a Comment