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!