2021 – Ensuring Uniqueness Part 2

Unique Array Values

The previous post in this series demonstrated maintaining unique scalar values in JSON data and Relational Data. In both cases uniqueness can be maintained with unique indexes. That sameness does not hold for array as this post will demonstrate.

This all comes about due to a new requirement for our patient tracking system:

Ordering System (OS) and Medical Record Number (MRN) combinations must be unique. For example only 1 patient can have an OS value of 1 with an MRN of 123. Any attempt to create duplicates must be rejected.

Here is how that is done in Relational data with a unique constraint (and underlying index) placed on the two columns. Any duplication will not be allowed.

ALTER TABLE patient_mrn
  ADD CONSTRAINT os_mrn_unique
  UNIQUE ( ordering_system, patient_mrn );

INSERT INTO patient VALUES(1,'Patient 1');
INSERT INTO patient_mrn VALUES(1,1,'MRN123');

INSERT INTO patient VALUES(2,'Patient 2');
INSERT INTO patient_mrn VALUES(1,2,'MRN123');
Raises ORA-00001: unique constraint (ME.OS_MRN_UNIQUE) violated

No change is required to the PL/SQL API because it already traps the unique constraint violation as a DUP_VAL_ON_INDEX exception. Here is the code for a reminder.

PROCEDURE add_patient ( p_name VARCHAR2,
                        p_os NUMBER,
                        p_mrn VARCHAR2 ) AS
BEGIN
  INSERT INTO patient(patient_id,
                      patient_name)
  VALUES(patient_seq.nextval,
         p_name);
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn)
  VALUES(patient_seq.currval,
         p_os,
         p_mrn);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE;
  WHEN OTHERS THEN NULL;
END;

And an example call for a combination OS=1 and MRN=MRN123 that already exists.

BEGIN
  patient_pkg.add_patient('Patient 3',1,'MRN123');
END;
Raises ORA-00001: unique constraint (ME.OS_MRN_UNIQUE) violated

JSON Is Different

Now for the JSON…

You’ll recall the last post added an index for unique patient names like this.

CREATE UNIQUE INDEX patient_unique
  ON json_patient ( JSON_VALUE(patient_data,'$.pname') );

INSERT INTO json_patient
VALUES('{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');

INSERT INTO json_patient
VALUES('{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');
Raises ORA-00001: unique constraint (ME.PATIENT_UNIQUE)

So, can we just use JSON_VALUE for the mrns array?

-- nope...
SELECT JSON_VALUE(patient_data,'$.mrns' ERROR ON ERROR)
  FROM json_patient;
Raises ORA-40456: JSON_VALUE evaluated to non-scalar value

JSON_VALUE only works in SCALAR values. Its does not work on arrays. But JSON_QIERY does…

SELECT JSON_QUERY(patient_data,'$.mrns') jquery
  FROM json_patient;

JQUERY
-----------------------------------------------------------
[ { "OS" : 1,"MRN" : "MRN123"},{"OS" : 2,"MRN" : "MRN456"}]

It correctly returned the two elements in the mrns array. Each array element contains two scalars; the OS and the MRN. It’s important to note there is no restriction or control on the number of elements in the array or the order of the elements in an array.

Lets see if creating an index on JSON_QUERY will work.

CREATE UNIQUE INDEX os_mrn_unique ON json_patient ( JSON_QUERY(patient_data,'$.mrns') );

And here is our current test data. One patient with 2 OS and MRN combinations.

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

Now I’ll try creating a new patient with the exact same OS/MRN combinations (OS=1 and MRN=MRN123 and OS=2 and MRN=MRN456).

INSERT INTO json_patient
VALUES('{"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');

Raises ORA-00001: unique constraint (ME.OS_MRN_UNIQUE)

Seems to have worked! But what if we insert the new patient with one OS/MRN combination that is a duplicate? Will the unique index catch it?

INSERT INTO json_patient
VALUES('{"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN123"}]}');

1 record insrted

That did not work. I wanted that to fail because OS 1 with mrn123 is already there… what happened? Lets look at the 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 2     1 MRN123
Patient 1     1 MRN123
Patient 1     2 MRN456

How did that get passed the unique index? Maybe JSON_QUERY was not the right choice.

SELECT JSON_QUERY(patient_data,'$.mrns') jquery
  FROM json_patient;
JQUERY
---------------------------------------------------------
[{"OS" : 1,"MRN" : "MRN123"}]
[{"OS" : 1,"MRN" : "MRN123"},{"OS" : 2,"MRN" : "MRN456"}]

Sure enough those strings do differ. We need to keep the array elements unique – not the whole array. To do that we have to rely on code in the PLSQL API like this:

PROCEDURE add_patient ( p_name VARCHAR2,
                         p_os NUMBER,
                         p_mrn VARCHAR2 ) IS

  -- cursor to look for the OS and MRN in the database
  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 VARCHAR2(100);

BEGIN

  -- if the OS and MRN already exists for any patient then refuse
  -- to carry on and raise an error
  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;

  v := JSON_OBJECT( KEY 'OS' VALUE p_os,
                    KEY 'MRN' VALUE p_mrn );
  v := JSON_ARRAY(v FORMAT JSON);
  v := JSON_OBJECT( KEY 'pname' VALUE p_name,
                    KEY 'mrns' VALUE v FORMAT JSON);
  INSERT INTO json_patient(patient_data) VALUES(v);

END;

And example calls that fail.

BEGIN
  jpatient_pkg.add_patient('Patient 3',1,'MRN123');
-- ORA-20000: OS/MRN Already Exists ORA-06512: at "ME.JPATIENT_PKG", line 27
END;

BEGIN
  jpatient_pkg.add_patient('Patient 3',2,'MRN456');
-- ORA-20000: OS/MRN Already Exists ORA-06512: at "ME.JPATIENT_PKG", line 27
END;

And one that succeeds.

BEGIN
  jpatient_pkg.add_patient('Patient 3',1,'MRN999');
END;

NB: Oracle 21 promises multicolumn indexes. Details here. I’ll investigate them in a future post.

Thanks for reading!

Leave a Comment