2021 – JSON Structure Changes With Database Unavailable

The patient management system has been given some new requirements.

  1. OS/MRN combinations must have a start date
  2. The start date defaults to the current date
  3. OS/MRN combinations may have and end date

In the RDBMS world this is done in 3 steps:

  1. Add New Columns
  2. Set New Column Values
  3. Set NULL

In JSON its done in one step as shown below. Here’s what the data looks like before we add anything.

-- Sample Data
SELECT JSON_QUERY(patient_data,'$.mrns') mrns
  FROM json_patient;
MRNS
-----------------------------------------------
[{"OS" : 1,"MRN" : "1"},{"OS" : 2,"MRN" : "2"}]
[{"OS" : 1,"MRN" : "3"},{"OS" : 2,"MRN" : "4"}]

I’ll write a PL/SQL function to add the new key value pairs.

CREATE OR REPLACE FUNCTION add_dates ( p_string VARCHAR2 ) RETURN VARCHAR2 AS
  v_ja JSON_ARRAY_T;
  v_lmnt JSON_ELEMENT_T;
  v_jo JSON_OBJECT_T;
  v_ret JSON_ARRAY_T;
BEGIN
  -- load up the local array
  v_ja := JSON_ARRAY_T.PARSE(p_string);
  -- for every entry in the array...
  FOR counter IN 0..v_ja.GET_SIZE - 1 LOOP
    -- get element from array and make it an object
    v_lmnt := v_ja.GET(counter);
    v_jo := JSON_OBJECT_T.PARSE(v_lmnt.STRINGIFY);
    -- put SDATE and PUT_NULL EDATE
    -- these will only PUT if the elements are not
    -- already there
    v_jo.PUT('SDATE',SYSDATE);
    v_jo.PUT_NULL('EDATE');
    -- put the object (with dates) right back
    -- where it came from
    v_ja.PUT(counter,v_jo,overwrite=>TRUE);
  END LOOP; -- every entry in the array
  RETURN(v_ja.stringify);
END;

And test it out with a query.

-- Test with a query
SELECT add_dates(JSON_QUERY(patient_data,'$.mrns')) mrns_with_dates
  FROM json_patient;
MRNS_WITH_DATES
---------------------------------------------------------------------------------------------------------------------------------------------
[{"OS" : 1,"MRN" : "1","SDATE" : "2021-08-07T14:02:20","EDATE" : null},{"OS" : 2,"MRN" : "2","SDATE" : "2021-08-07T14:02:20","EDATE" : null}]
[{"OS" : 1,"MRN" : "3","SDATE" : "2021-08-07T14:02:20","EDATE" : null},{"OS" : 2,"MRN" : "4","SDATE" : "2021-08-07T14:02:20","EDATE" : null}]

That works perfectly. I’ll be using JSON_MERGEPATCH to do the actual update. Here is what the results will look like.

SELECT JSON_MERGEPATCH(patient_data,
       '{"mrns":' || add_dates(JSON_QUERY(patient_data,'$.mrns')) ||'}') all_with_dates
  FROM json_patient
ALL_WITH_DATES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"pname" : "Patient 1",[{"OS" : 1,"MRN" : "1","SDATE" : "2021-08-07T14:02:20","EDATE" : null},{"OS" : 2,"MRN" : "2","SDATE" : "2021-08-07T14:02:20","EDATE" : null}]}
{"pname" : "Patient 2",[{"OS" : 1,"MRN" : "3","SDATE" : "2021-08-07T14:02:20","EDATE" : null},{"OS" : 2,"MRN" : "4","SDATE" : "2021-08-07T14:02:20","EDATE" : null}]}

That looks good. Now for the update…

UPDATE json_patient
SET patient_data = JSON_MERGEPATCH(patient_data,
            '{"mrns":' || add_dates(JSON_QUERY(patient_data,'$.mrns')) ||'}');
2 row(s) updated

And a query to see the results.

SELECT JSON_VALUE(patient_data,'$.mrns[0].SDATE') p1_sdate,
       JSON_VALUE(patient_data,'$.mrns[0].EDATE') p1_edate,
       JSON_VALUE(patient_data,'$.mrns[1].SDATE') p2_sdate,
       JSON_VALUE(patient_data,'$.mrns[1].EDATE') p2_edate
  FROM json_patient;
P1_SDATE            P1_EDATE P2_SDATE            P2_EDATE
------------------- -------- ------------------- --------
2021-08-07T14:19:17          2021-08-07T14:19:17
2021-08-07T14:19:17          2021-08-07T14:19:17

What happens if we run the update again?

UPDATE json_patient
SET patient_data = JSON_MERGEPATCH(patient_data,
             '{"mrns":' || add_dates(JSON_QUERY(patient_data,'$.mrns')) ||'}');
2 row(s) updated

The results do not change because the PUT call in the function will not add the new keys if they already exist.

SELECT JSON_VALUE(patient_data,'$.mrns[0].SDATE') p1_sdate,
       JSON_VALUE(patient_data,'$.mrns[0].EDATE') p1_edate,
       JSON_VALUE(patient_data,'$.mrns[1].SDATE') p2_sdate,
       JSON_VALUE(patient_data,'$.mrns[1].EDATE') p2_edate
  FROM json_patient;
P1_SDATE            P1_EDATE P2_SDATE            P2_EDATE
------------------- -------- ------------------- --------
2021-08-07T14:19:17          2021-08-07T14:19:17
2021-08-07T14:19:17          2021-08-07T14:19:17

In the next post I’ll cover what is required for the PL/SQL API.

Thanks for reading!

Leave a Comment