2021 – Add Entry To JSON Array

The previous post in this series satisfied the following requirement in RDBMS data.

Create a new record in a child table that is related to the parent table via a Foreign Key (FK) to a Primary Key (PK) in the parent table.

Its much simpler to describe in terms of JSON data:

Add a new entry to the mrn array.

So is the code simpler? Let’s work through it.

Here is the test bed of JSON data.

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     1 MRN123
Patient 1     2 MRN456
Patient 2     1 MRN99

The gain in simplicity to describe the JSON data change is offset by the increase in complexity of the code to do it. Here is the JSON procedure.

CREATE OR REPLACE PROCEDURE add_mrn ( p_name VARCHAR2,
                                      p_os NUMBER,
                                      p_mrn VARCHAR2 ) IS

  -- cursor to see if the OS/MRN combination is already in the DB
  CURSOR os_mrn_already_there ( cp_os  NUMBER,
                                cp_mrn VARCHAR ) IS
  SELECT NULL
    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' )))
    WHERE os = cp_os
      AND mrn = cp_mrn;

  v_dummy VARCHAR2(1);
  v_txt   VARCHAR2(1000);
  v_arr   JSON_ARRAY_T;
  v_obj   JSON_OBJECT_T;

BEGIN

  -- check if the OS/MRN is already in the Database and bail if it is
  OPEN os_mrn_already_there(p_os,p_mrn);
  FETCH os_mrn_already_there INTO v_dummy;
  IF os_mrn_already_there%FOUND THEN
    RAISE_APPLICATION_ERROR(-20000,'OS/MRN Already Exists');
  END IF;
  CLOSE os_mrn_already_there;

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

  -- turn the array into a JSON_ARRAY_T variable
  v_arr := JSON_ARRAY_T.PARSE(v_txt);

  -- create a JSON_OBJECT_T with the new OS and new MRN
  v_obj := JSON_OBJECT_T.PARSE('{"OS":' || p_os || ',"MRN":"' || p_mrn || '"}');

  -- append the object to the array
  -- aka: add the new OS/MRN object to the end of the mrn JSON array
  v_arr.append(v_obj);
  v_txt := v_arr.stringify;

  -- update the database record with the new array
  UPDATE json_patient
  SET patient_data = JSON_MERGEPATCH(patient_data,'{"mrns":' || v_txt ||'}')
  WHERE JSON_VALUE(patient_data,'$.pname') = p_name;

END;

And a test call.

add_mrn('Patient 1',99,'MRN9876');

And now the data looks like this:

PATIENT_NAME OS MRN
------------ -- -------
Patient 1     1 MRN123
Patient 1     2 MRN456
Patient 1    99 MRN9876
Patient 2     1 MRN99

Try to add the same OS/MRN again. It’s trapped by the cursor in the procedure and an error is raised.

add_mrn('Patient 1',99,'MRN9876');

ORA-20000: OS/MRN Already Exists ORA-06512: at "ME.ADD_MRN", line 27

That seems like a lot of steps in JSON:

  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

Much more work than the “Insert Into Select * From” (IISSF) for the RDBMS data. The number of steps is reduced in Oracle 21c with the JSON_TRANSFORM function but the complexity remains. I’ll cover JSON_TRANSFORM in a future post. I’ll also explain some of the other complexities of JSON data and how to overcome – or at least reduce – them.

Thanks for reading!

Leave a Comment