Not content to just put EMPI keys and values in disparate places in the JSON some applications started entering EMPI as objects.
INSERT INTO json_patient VALUES('{"EMPI":{"ISSUER":"HOSPITALB","NUMBER":88}}');
My table function tried its best…
SELECT *
FROM TABLE(add_empi)
WHERE empi = '$.EMPI';
PATIENTDATA EMPI
---------------------------------------------- ---------------------------------
{"pname":"Rosco Coltrane1","EMPI":1} $.EMPI
{"EMPI":{"ISSUER":"HOSPITALB","NUMBER":88}} $.EMPI
It did indeed find EMPI but its an object not a value. So I gave up. No I didn’t! I soldiered on because…I need the paycheck. Luckily I can get the object type from JSON_DATAGUIDE in my function as shown in the code below.
/*--------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION find_empi ( p_json JSON )
RETURN VARCHAR2
DETERMINISTIC IS
/*--------------------------------------------------------------*/
-- query the JSON columns path (number, date, object, etc) and
-- the path eg $.EMPI
CURSOR jcurs ( cp_json JSON ) IS
WITH dg_table AS ( SELECT JSON_DATAGUIDE(cp_json) dg_doc
FROM DUAL )
SELECT guide_path.path,
JSON_OBJECT(guide_path.path,guide_path.type)
FROM dg_table,
JSON_TABLE(dg_doc, '$[*]'
COLUMNS path VARCHAR2(40) PATH '$."o:path"',
type VARCHAR2(40) PATH '$."type"') guide_path;
v_jpath VARCHAR2(40);
v_jtype VARCHAR2(40);
v_ret_val VARCHAR2(100) := 'NO EMPI';
BEGIN
-- loop through the data guide looking for EMPI
OPEN jcurs(p_json);
LOOP
FETCH jcurs INTO v_jpath, v_jtype;
IF v_jpath LIKE '%.EMPI' THEN
v_ret_val := v_jtype;
EXIT;
END IF;
EXIT WHEN jcurs%NOTFOUND;
END LOOP;
CLOSE jcurs;
RETURN(v_ret_val);
END;
And now the query.
SELECT *
FROM TABLE(add_empi);
PATIENTDATA EMPI
------------------------------------------- ------------------------------------
{"pname":"Rosco Coltrane1","EMPI":1} {"path":"$.EMPI","type":"number"}
{"pname":"Enos Strate1","mrns":{"EMPI":1}} {"path":"$.mrns.EMPI","type":"number"}
{"pname":"Boss Hogg1"} NO EMPI
{"pname":"Uncle Jessie1"} NO EMPI
{"EMPI":{"ISSUER":"HOSPITALB","NUMBER":88}} {"path":"$.EMPI","type":"object"}
This tells me the EMPI in the last row is an JSON object and not data. Thus I need to navigate further down to get to the actual value. In the next post I’ll show how to do that.
Thanks