2021 – What If Its Not An Array?

The last few posts in this series covered adding entries to a JSON array named mrns using SQL and PLSQL. It even handled cases where the mrns array did not exist yet. But what if it did exist but it was not an array? JSON storage is quite flexible we have to gird our code for that challenge.

Here is an example record with an mrn element that is a scalar value instead of the array our app is expecting.

-- create a patient with an mrns element that is not an array
INSERT INTO json_patient
VALUES('{"pname":"Patient 1","mrns":"notanarray"}');
-- { "pname" : "Patient 1","mrns" : "notanarray"}

Now we try to add an entry using the PLSQL API.

BEGIN
  add_mrn('Patient 1',1,'MRN22');
END;

And what does the data look like afterwards?

SELECT *
FROM json_patient;
{"pname" : "Patient 1","mrns" : [{"OS" : 1,"MRN" : "MRN22"}]}

Wow! Looks like we dodged a bullet. We were saved by the EMPTY ARRAY ON ERROR clause on this query in the add_mrn procedure.

SELECT JSON_QUERY(patient_data,'$.mrns' EMPTY ARRAY ON ERROR)
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient New';

It encountered an error and returned an EMPTY array which was later populated with the new element. But what error did the query raise? After resetting the data we can run the query in SQL specifying ERROR ON ERROR.

SELECT JSON_QUERY(patient_data,'$.mrns' ERROR ON ERROR )
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient 1';
ORA-40480: result cannot be returned without array wrapper

JSON_QUERY is not able to return scalar values so we have to wrap the result.

SELECT JSON_QUERY(patient_data,'$.mrns' WITH CONDITIONAL ARRAY WRAPPER )
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient 1';
["notanarray"]

Just for funsies let’s add the wrapper clause to the add_mrn procedure to see what we get.

BEGIN
  add_mrn('Patient 1',1,'MRN22');
END;

And now query the data.

SELECT JSON_QUERY(patient_data,'$.mrns' ERROR ON ERROR ) mrns
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient 1';
MRNS
------------------------------------------
[ "notanarray",{"OS" : 1,"MRN" : "MRN22"}]

Here is what happened in the procedure.

  1. The query found a scalar value (“notanarray”)¬† and put an array wrapper around it
  2. The new entry was added to the array

So we have a valid array but it contains a mix of scalar and non-scalar values. That’s not what we want in our data.

Lets reset the data with records to test relevant fixes.

-- Scenario 1 - an mrns element that is a scalar (not an array)
--  We want attempts to add an OS/MRN to fail
INSERT INTO json_patient
VALUES('{"pname":"Patient 1","mrns":"notanarray"}');

-- Scenario 1 - a record with no mrns element at all
-- We want the OS/MRN to be added
INSERT INTO json_patient
VALUES('{"pname":"Patient 2"}');

-- Scenario 3 - a valid record with a valid mrns array
-- We want the OS/MRN to be added
INSERT INTO json_patient
VALUES('{"pname":"Patient 3","mrns":[{"OS":1,"MRN":"MRN99"}]}');

In the procedure I’ll expand the query to use JSON Path Expression Item Method (JPEIM) to check the element type.

SELECT JSON_VALUE(patient_data,'$.pname')               pname,
       JSON_QUERY(patient_data,'$.mrns' EMPTY ON ERROR ) mrns,
       JSON_VALUE(patient_data,'$.mrns.type()')          json_type
  FROM json_patient;
PNAME     MRNS                         JSON_TYPE
--------- ---------------------------- ---------
Patient 1 []                           string
Patient 2 []
Patient 3 [{"OS" : 1,"MRN" : "MRN99"}] array
  • Scenario 1 returns a type of string (not an array)
  • Scenario 2 returns a type of undefined
  • Scenario 3 returns a type of array

Now lets update the procedure to react the string type.

-- get the current mrn array for the patient
SELECT JSON_QUERY(patient_data,'$.mrns' EMPTY ON ERROR ),
       JSON_VALUE(patient_data,'$.mrns.type()')
  INTO v_txt,
       v_type
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = p_name;

IF v_type = 'string' THEN
  RAISE_APPLICATION_ERROR(-20000,'Invalid MRNs entry');
END IF;

Now test it…

-- Scenario 1 = string; not an array
-- Expecting = Raises Invalid MRNs entry
BEGIN
  add_mrn('Patient 1',1,'MRN8888');
END;

Correct. Next…

-- Scenario 2 = no mrn element
-- Expecting = OS/MRN gets added
BEGIN
  add_mrn('Patient 2',1,'MRN3456');
END;

Correct again. Next…

-- Scenario 3 = mrn array exists
-- Expecting = OS/MRN gets added
BEGIN
  add_mrn('Patient 3',1,'MRN789');
END;

And the data afterwards.

SELECT patient_name,
       os,
       mrn
  FROM json_patient,
       JSON_TABLE(patient_data, '$'
         COLUMNS ( patient_name VARCHAR2 PATH '$.pname',
           NESTED PATH '$.mrns[*]'
             COLUMNS ( os  VARCHAR2(30) PATH '$.OS',
                       mrn VARCHAR2(30) PATH '$.MRN' )))
PATIENT_NAME OS MRN
------------ -- -------
Patient 1
Patient 2     1 MRN3456
Patient 3     1 MRN99
Patient 3     1 MRN789

Success! Thanks for reading!

Leave a Comment