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!