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 asks his Boss if this is a test system. Nope – that’s production. Somewhat bewildered he digs deeper. That table must have millions of columns to store all of the data…

SQL> desc patient_data
Name               Null?    Type
------------------ -------- --------
PATIENT_DATA_PK    NOT NULL NUMBER
EVERYTHING_ELSE             JSON

Nope. Just two columns…

“Oh no…” he wonders “what have I gotten myself into? I don’t know how to JSON!” But remembering his favorite phrase – “Its all about the data” he dares to query the JSON and here is what he sees.

{"Name":"Roscoe",
 "EMPI":10101,
 "MRNS":[{"Site":"Hospital","MRN":443},
         {"Site":"Clinic","MRN":119},
         {"Site":"Lab","MRN":808}]}

{"Name":"Roscoe",
 "EMPI":10101,
 "MRNS":[{"Site":"Lab","MRN":8433}]}

{"Name":"Enos",
 "EMPI":20202,
 "MRNS":[{"Site":"Hospital","MRN":449},
         {"Site":"Clinic","MRN":114},
         {"Site":"Lab","MRN":8089}]}

{"Name":"Daisy",
 "EMPI":30303,
 "MRNS":[{"Site":"Outpatient","MRN":36}]}

That’s really different from relational data. Why is it all on one column? Why is the patient data not unique per row? Before he has a chance to ponder further he receives his first task.

Write a PL/SQL function to return all sites associated with a particular EMPI value.

For example the single record for Enos would return this.

["Hospital","Clinic","Lab"]

Armed with decades of SQL and PLSQL experience and the Oracle JSON documentation he quickly produces this function.

CREATE OR REPLACE FUNCTION sites ( p_mrns JSON )
                   RETURN JSON AS

  v_array  JSON_ARRAY_T;
  v_array2 JSON_ARRAY_T;
  v_size   NUMBER;
  v_lmnt   JSON_ELEMENT_T;
  v_jo     JSON_OBJECT_T;

  v_json   JSON;

BEGIN

  /*
     initialize arrays...
     one to hold JSON passed in...
     "MRNS":[{"Site":"Hospital","MRN":443},
             {"Site":"Clinic","MRN":119},
             {"Site":"Lab","MRN":808}]
     ...and one for the JSON returned
     ["Hospital","Clinic","Lab"]
  */
  v_array  := JSON_ARRAY_T(p_mrns);
  v_array2 := JSON_ARRAY_T();

  -- parse the in array and load the out array
  v_size := v_array.get_size;
  v_size := v_size - 1;
  FOR counter IN 0..v_size LOOP
    v_lmnt := v_array.GET(counter);
    v_jo := TREAT (v_lmnt AS json_object_t);
    v_lmnt := v_jo.GET('Site');
    v_array2.APPEND(v_lmnt);
  END LOOP;
  v_json := v_array2.TO_JSON;
  RETURN(v_json);

END;

And here’s his function in action.

SELECT JSON_VALUE(everything_else,'$.Name') nm,
        sites(JSON_QUERY(everything_else,'$.MRNS')) sites
   FROM patient_data;

NM          SITES
----------  ------------------------------
Roscoe      ["Hospital","Clinic","Lab"]
Roscoe      ["Lab"]
Enos        ["Hospital","Clinic","Lab"]
Daisy       ["Outpatient"]

He shows his Boss who approves the work! Austin’s JSON career is of to a great start. This JSON stuff seems pretty simple.

This series of articles will follow Austin as he learns more about switching from Relational data to JSON.

Thanks for reading.

Leave a Comment