SQL Macros Part 2

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