2021 – Add Entry To JSON Array That Does Not Exist Yet

The previous post created a procedure to add an entry to a JSON array. This is comparable to the RDBMS situation where no child records exist yet. But (…there is always a but…) for RDBMS the code is exactly the same for first records and all subsequent ones. The JSON code, however requires some adaptation as shown in this post.

To demonstrate I’ll create a JSON patient record with no mrn array.

-- Create a record with no mrns array
INSERT INTO json_patient
VALUES('{"pname":"Patient New"}');

Next I’ll call the add_mrn procedure to add an entry to the mrn array.

The procedure code is here

BEGIN
  add_mrn('Patient New',1,'MRNNEW');
END;
ORA-40834: invalid input to JSON parse/load function ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_ARRAY_T", line 102
ORA-06512: at "ME.ADD_MRN", line 40

Three successive steps cause the error.

  1. This query found a record.
SELECT JSON_QUERY(patient_data,'$.mrns')
  INTO v_txt
  FROM json_patient
 WHERE JSON_VALUE(patient_data,'$.pname') = p_name;

2. …but the mrns array does not exist for the record so v_txt wound up being NULL so…

3. ..the subsequent call to parse the array failed because NULL is considered invalid input

v_arr := JSON_ARRAY_T.PARSE(v_txt);

If no mrns array exists the initial query returns nothing. It does not fail. It just…returns…nothing. And then parsing this nothing throws the exception. The trick here is to return an empty array instead of nothing. This is done by adding the EMPTY ARRAY ON ERROR clause as shown here.

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

The mrns element not existing is considered an error so the ON ERROR clause is invoked and we have told it to return an EMPTY array when this happens. An empty array is square brackets []. This allows the subsequent append operation to add an entry. Thus the add_mrn procedure will now work!

BEGIN
  add_mrn('Patient New',1,'MRNNEW');
END;

A quick test to verify.

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

So the overall algorithm for the procedure remains the same:

  1. Query the array
  2. Create an array entry with the new OS and MRN
  3. Add the new entry to the array
  4. Update the database record

Even if the array did not exist at the start.

Thanks for reading!

Leave a Comment