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 has proven to not be that simple so I cant rely on patient names being unique.
  • EMPI
    • Enterprise Master Patient Indexes will be used to group applicable patient records together.

Here is the DDL to create the JSON patient table.

CREATE TABLE json_patient
( patient_data CLOB,
    CONSTRAINT json_please
      CHECK ( patient_data IS JSON ) );

CREATE UNIQUE INDEX patient_unique
ON json_patient ( JSON_VALUE(patient_data,'$.pname') );

Patient Names Are Not Unique

And now the data with non-unique patient names.

INSERT INTO json_patient
VALUES('{"pname":"Rosco Coltrane","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');

INSERT INTO json_patient
VALUES('{"pname":"Enos Strate","mrns":[{"OS":1,"MRN":"MRN88"}]}');

INSERT INTO json_patient
VALUES('{"pname":"Rosco Coltrane","mrns":[{"OS":1,"MRN":"MRN99"}]}');
-- Raises ORA-00001: unique constraint (ADMIN.PATIENT_UNIQUE) violated

I need to remove the unique constraint before the second Rosco can be added. I’ll keep the index for performance.

ALTER TABLE json_patient
DROP CONSTRAINT patient_unique;

CREATE INDEX patient_search ON json_patient ( JSON_VALUE(patient_data,'$.pname') );

Enterprise Master Patient Index (EMPI)

Tracking which patient records are for the same human is done with EMPI number linking one or more patient records to a single unique EMPI number.

First I’ll create sequence for seeding the EMPI numbers


And a function to wrap the sequence call.

                   RETURN NUMBER AS

Add an EMPI key and value to each patient that does not have one.

UPDATE json_patient
  SET patient_data = JSON_TRANSFORM(patient_data,
                     SET '$.EMPI' = empi_seq_function
                     IGNORE ON EXISTING );

I wont add the obsolete flag until it is required by patient merges later.

And here is the resultant data.

SELECT JSON_VALUE(patient_data,'$.pname') name,
        JSON_VALUE(patient_data,'$.EMPI') empi,
        JSON_QUERY(patient_data,'$.mrns') mrns

NAME            EMPI MRNS
--------------- ---- -------------------------------------------------
Enos Strate     16   [{"OS":1,"MRN":"MRN88"}]
Rosco Coltrane  18   [{"OS":1,"MRN":"MRN99"}]
Rosco Coltrane  20   [{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]

I assigned 1 EMPI number to each patient record to seed the database. Later on I will build a PL/SQL function merge them as required. For now I’m happy to have the new tables in place.

Thanks for reading!

Leave a Comment