Austin Tatious JSON Developer (A Fresh Start)

After several decades working with Relational Data world-renowned developer Austin Tatious has started a new position working with JSON. He’s working for a patient patient management software company. Being a self starter he dives right in to check what tables exist in the database. SELECT table_name FROM user_tables; TABLE_NAME —————————— PATIENT_DATA Only one table? He … Read more

SQL Macros Part 2

Not content to just put EMPI keys and values in disparate places in the JSON some applications started entering EMPI as objects. INSERT INTO json_patient VALUES(‘{“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}}’); My table function tried its best… SELECT * FROM TABLE(add_empi) WHERE empi = ‘$.EMPI’; PATIENTDATA EMPI ———————————————- ——————————— {“pname”:”Rosco Coltrane1″,”EMPI”:1} $.EMPI {“EMPI”:{“ISSUER”:”HOSPITALB”,”NUMBER”:88}} $.EMPI It did indeed find EMPI but … Read more

2022 – SQL Macros Introduction

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 … Read more

2022 – JSON Search Index Synchronization

Another option for synchronizing JSON Search Indexes is Manual which is enabled by altering the meta-data of the index like this: ALTER INDEX jindex PARAMETERS (‘replace metadata sync (manual)’); Now when DML occurs… INSERT INTO jtable VALUES(‘{“JTOP”:3}’); SELECT * FROM jtable WHERE JSON_EXISTS(jcol,’$.JTOP’); JCOL ——————- {“JTOP”:1} {“JTOP”:2} It will not show up in queries (even … Read more

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 … Read more

2021 – Complex Structure Changes 1 – RDBMS

Its time to deal with the inevitable complex changes to the database. I’ll start working through the RDBMS 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 … Read more

2021 – Large Data Structure Changes

Previous posts comparing JSON and RDBMS structure changes used a simple example adding RDBMS date columns or JSON keys. Now its time for something more challenging: Enterprise Master Patient Index (EMPI) All Patients must be given a new master identifier encompassing one or more patient records and their associated Medical Record Numbers (MRN’s) If the … Read more

2021 – Simply Evolving JSON Structure (Updates)

JSON_TRANSFORM can ensure JSON structure when records are inserted into the database using an INSTEAD OF INSERT trigger on a view. CREATE OR REPLACE TRIGGER instead_patient INSTEAD OF INSERT ON migrated_patient BEGIN INSERT INTO json_patient VALUES ( JSON_TRANSFORM(:new.migrated, SET ‘$.unique_os’ = OS_LIST_UNIQUE( JSON_QUERY(:new.migrated,’$.mrns[*].OS’ WITH WRAPPER) ) REPLACE ON EXISTING, INSERT ‘$.mrns[*].sdate’ = SYSDATE IGNORE ON … Read more