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 – 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

2021 – JSON_TRANSFORM And Custom PL/SQL

My prior posts on JSON_TRANSFORM used simple text values and built-in Oracle functions like SYSDATE. This post demonstrates calling custom PL/SQL functions with JSON_TRANSFORM. Here are the two rows of demo data for this post. The pertinent keys are pname (patient name) and OS (Ordering System) in the elements of the mrns array. SELECT JSON_VALUE … Read more