implestrat.com

This post continues our quest to find Bad JSON data while reducing code complexity. In particular we hid many lines of complex PL/SQL behind a simple to understand and use Virtual Column (VC). The previous post explained the theory and syntax for VC’s – this post continues our implementation of them to find bad data. … Read more

The previous post in this series showed SQL and PLSQL code to find bad JSON data and then fix that bad data. It required many more lines of code (and complexity) than the RDBMS Version for the same functionality. This post begins introducing ways to simplify the “finding bad JSON data” using an Oracle feature … Read more

After a few weeks using our JSON patient management system the users have noticed patients with more than one MRN in a single Ordering System. This should not have been allowed to happen. We’ll put something in place later to prevent future occurrences. For this post we will look at the cleanup. Here’s an example … Read more

This post compares the coding approach to fixing bad data in an RDBMS and JSON system. First up is the RDBMS version… After using our patient management system for a few weeks the users started noticing patients with multiple MRN’s in a single Ordering System. This is happening because we forgot to add a unique … Read more

The last few posts in this series covered adding entries to a JSON array named mrns using SQL and PLSQL. It even handled cases where the mrns array did not exist yet. But what if it did exist but it was not an array? JSON storage is quite flexible we have to gird our code … Read more

The previous post created a procedure to add an entry to a JSON array. This is comparable to the RDBMS situation where no child records exist yet. But (…there is always a but…) for RDBMS the code is exactly the same for first records and all subsequent ones. The JSON code, however requires some adaptation … Read more

The previous post in this series satisfied the following requirement in RDBMS data. Create a new record in a child table that is related to the parent table via a Foreign Key (FK) to a Primary Key (PK) in the parent table. Its much simpler to describe in terms of JSON data: Add a new … Read more

This post explains how a child record is added for a parent record. These terms (parent and child record) are part of the Relational Database Management System (RDBMS) nomenclature. The fully expanded description would be: Create a new record in a child table that is related to the parent table via a Foreign Key (FK) … Read more