2021 – Simply Evolving JSON Structure (Queries)

The JSON_TRANSFORM function is perfect for evolving JSON structures to provide consistency. Take the patient tracking system being used for these posts for example. The first version did not have checks for unique OS values or start and end dates for OS/MRN pairs. Adding these keys is easily done as shown in this example. I can even hide the relative complexity behind an Oracle view.

Here is the demo data for this post. The date and unique_os keys (or lack thereof) in the mrns array are the focal point here.

SELECT JSON_QUERY(patient_data,'$') demo
  FROM json_patient;
{"pname":"No Dates","mrns":[{"OS":1,"MRN":"MRN99"}]}
{"pname":"Just SDate","mrns":[{"OS":1,"MRN":"MRN99",

The objective in this post is threefold:

  1. Add a unique_os key populated by the OS_LIST_UNIQUE function Add this each and every time – whether it exists already or not.
  2. Add an sdate key with a default value of the current date (SYSDATE). Only add if it’s not already there.
  3. Add an edate key with a value of NULL. Only add if it’s not already there.

To simplify subsequent calls I’ll create a view to do all the work.

CREATE VIEW migrated_patient AS
           SET '$.unique_os' =
               OS_LIST_UNIQUE( JSON_QUERY(patient_data,'$.mrns[*].OS' WITH WRAPPER) )
               REPLACE ON EXISTING,
           INSERT '$.mrns[*].sdate' = SYSDATE IGNORE ON EXISTING,
           INSERT '$.mrns[*].edate' = NULL    IGNORE ON EXISTING ) migrated
   FROM json_patient;

The view meets the 3 objectives as follows:

  1. SET with REPLACE ON EXISTING adds and populates the new unique_os key or overwites the existing one. This ensures the value is calculated every time the view is queried.
  2. INSERT with IGNORE ON EXISTING adds and populates the sdate key if it does not exist.
  3. INSERT with IGNORE ON EXISTING adds and populates the edate key if it does not exist.

And now I just query the view (easy-peasy) to see all the changes.

  FROM migrated_patient;
{"pname":"No Dates","mrns":[{"OS":1,"MRN":"MRN99",
{"pname":"Just SDate","mrns":[{"OS":1,"MRN":"MRN99",

Thanks for reading!

Leave a Comment