Project 2021 requires 2 different types of database structures.
- Relational (RDBMS)
- Tables with rows of columns and integrity checks
- JSON
- All data stored in a single column with embedded KEY:VALUE pairs
RDBMS Structure And Data
Here is the SQL to create and populate the RDBMS structure.
CREATE TABLE patient ( patient_id NUMBER NOT NULL PRIMARY KEY, patient_name VARCHAR2(30) NOT NULL); CREATE TABLE patient_mrn ( ordering_system NUMBER NOT NULL, patient_id NUMBER NOT NULL, patient_mrn VARCHAR2(30) NOT NULL, CONSTRAINT mrn_to_patient FOREIGN KEY ( patient_id ) REFERENCES patient); -- create 1 patient with 2 mrns INSERT INTO patient VALUES(1,'Patient 1'); INSERT INTO patient_mrn VALUES(1,1,'MRN123'); INSERT INTO patient_mrn VALUES(2,1,'MRN456'); -- create another patient with 1 mrn INSERT INTO patient VALUES(2,'Patient 2'); INSERT INTO patient_mrn VALUES(1,2,'MRN99');
JSON Structure and Data
Here is the SQL to create and populate the JSON structure.
CREATE TABLE json_patient ( patient_data CLOB ); -- 1 patient with 2 mrns INSERT INTO json_patient VALUES('{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}'); -- Another patient with 1 mrn INSERT INTO json_patient VALUES('{"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}');
Query RDBMS Data
And here is an SQL query of the relational data.
SELECT patient_name, ordering_system, patient_mrn FROM patient p, patient_mrn pm WHERE p.patient_id = pm.patient_id; PATIENT_NAME ORD PATIENT_MRN ------------ --- ----------- Patient 1 1 MRN123 Patient 1 2 MRN456 Patient 2 1 MRN99
Query JSON Data
And here is an SQL query of the JSON data using the JSON_VALUE SQL function to translate KEY:VALUE pairs into columns of rows to match (somewhat) the results of the RDBMS query.
SELECT JSON_VALUE(patient_data,'$.pname' NULL ON ERROR ) pname, JSON_VALUE(patient_data,'$.mrns[0].OS' NULL ON ERROR ) frstos, JSON_VALUE(patient_data,'$.mrns[0].MRN' NULL ON ERROR ) frstmrn, JSON_VALUE(patient_data,'$.mrns[1].OS' NULL ON ERROR ) scndos, JSON_VALUE(patient_data,'$.mrns[1].MRN' NULL ON ERROR ) scndmrn FROM json_patient;
PNAME FRSTOS FRSTMRN SCNDOS SCDNMRN ------------ ------ ------- ------ ------- Patient 1 1 MRN123 2 MRN456 Patient 2 1 MRN99
So the starting data is the same. Or is it? I would hate to get to post 20 in this series only to discover the data differed in post 2. Thus the next few posts will show how to (and not to) compare JSON and Relational data.
Thanks for reading!