Austin Tatious – Uniqueness Required

Buoyed by his early success Austin sets out for some thorough function testing. He eventually comes across this result.

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

NM SITES
---------- --------------------------------------------------
Cooter ["Hospital","Hospital"]

The underlying JSON is this:

{"Name":"Cooter",
 "EMPI":653998,
 "MRNS":[{"Site":"Hospital","MRN":8753},
         {"Site":"Hospital","MRN":44428}]}

This patient has two MRN’s from Hospital but the site name only needs to be returned once like this.

NM         SITES
---------- ------------------
Cooter     ["Hospital"]

Here is the code Austin writes.

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

  v_array JSON_ARRAY_T;
  v_array2 JSON_ARRAY_T;
  v_size NUMBER;
  v_lmnt JSON_ELEMENT_T;
  v_jo JSON_OBJECT_T;
 
  TYPE v_vc2_t IS TABLE OF NUMBER
    INDEX BY VARCHAR2(30);
  v_unique_sites v_vc2_t := v_vc2_t();

  v_json JSON;

BEGIN

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

  -- parse the IN array and load the INDEX BY table
  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_unique_sites(v_lmnt.TO_STRING) := 1;
  END LOOP;

  -- Now assemble the out array from the INDEX BY table
  DECLARE
    v VARCHAR2(30);
  BEGIN
    v := v_unique_sites.FIRST;
    LOOP
      EXIT WHEN v IS NULL;
      v_jo := JSON_OBJECT_T.PARSE('{"WTF":' || v || '}');
      v_array2.APPEND(v_jo.get('WTF'));
      v := v_unique_sites.NEXT(v);
    END LOOP;
  END;

  v_json := v_array2.TO_JSON;

RETURN(v_json);

END;

And now the result is:

NM SITES
---------- --------------------------------------------------
Cooter ["Hospital"]

Truth be told Austin struggled with that function due to unexpected (…to Austin at least…) escape characters. The next post will explain that.

Thanks for reading!