2021 – Compare JSON and RDBMS Data 2

The last post showed the pitfalls of treating JSON as simple text when comparing it to relational data. This post demonstrates a better way to perform the comparison.

  1. Use built in SQL and PLQL functions to translate the relational data to JSON
  2. Perform a “JSON aware” comparison to the JSON data

Relational To JSON

Step 1 is done using Oracle JSON SQL functions. The first SQL function is JSON_OBJECT which creates a JSON object (surrounded by {brackets}) for provided key-value pairs. Here is the code to generate the Ordering System (OS) and Medical Record Number (MRN) objects for our patient system.

SELECT JSON_OBJECT( KEY 'OS' IS  pm.ordering_system,
                    KEY 'MRN' IS pm.patient_mrn ) jsonobject
  FROM patient_mrn pm;

JSONOBJECT
---------------------------
{"OS" : 1,"MRN" : "MRN123"}
{"OS" : 2,"MRN" : "MRN456"}
{"OS" : 1,"MRN" : "MRN99"}

The next step is to aggregate all of the OS/MRN combinations into an array using the JSON_ARRAYAGG function. The resultant array is then made part of an object named mrns with this SQL.

SELECT JSON_OBJECT( KEY 'mrns' IS JSON_ARRAYAGG(
                                  JSON_OBJECT( KEY 'OS' IS pm.ordering_system,
                                               KEY 'MRN' IS pm.patient_mrn ))) jsonobject
  FROM patient_mrn pm
  WHERE patient_id = 1;

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

That matches the JSON data from the JSON_PATIENT table.

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

To complete the comparison we need to add the patient name to the object.

SELECT JSON_OBJECT( KEY 'pname' IS patient_name,
                    KEY 'mrns' IS JSON_ARRAYAGG(
                                  JSON_OBJECT( KEY 'OS' IS pm.ordering_system,
                                               KEY 'MRN' IS pm.patient_mrn ))) jsonobject
  FROM patient_mrn pm,
       patient p
 WHERE p.patient_id = pm.patient_id
GROUP BY p.patient_id, p.patient_name;

JSONOBJECT
------------------------------------------------------------------------------------------
{"pname" : "Patient 1","mrns" : [{"OS" : 1,"MRN" : "MRN123"},{"OS" : 2,"MRN" : "MRN456"}]}
{"pname" : "Patient 2","mrns" : [{"OS" : 1,"MRN" : "MRN99"}]}

That looks really close to what is in the JSON_PATIENT table! I’ll run a comparison now using PL/SQL.

Comparing Using JSON_EQUAL

I’ll run a comparison now using PL/SQL.

DECLARE
  v_clob CLOB;
BEGIN
  -- for every relational record - translate it to JSON
  FOR v_gen IN ( SELECT JSON_OBJECT( KEY 'pname' IS patient_name,
                                     KEY 'mrns' IS JSON_ARRAYAGG(
                        JSON_OBJECT( KEY 'OS' IS pm.ordering_system,
                                     KEY 'MRN' IS pm.patient_mrn ))) gen_json
                   FROM patient_mrn pm,
                        patient p
                  WHERE p.patient_id = pm.patient_id
                 GROUP BY p.patient_id, p.patient_name ) LOOP

    -- output the generated JSON object
    DBMS_OUTPUT.PUT_LINE('GEN=' || v_gen.gen_json);

    -- get the JSON data from the database
    -- match on pname
    SELECT patient_data
      INTO v_clob
      FROM json_patient
     WHERE JSON_VALUE(patient_data,'$.pname') = JSON_VALUE(v_gen.gen_json,'$.pname');

    -- Use JSON_EQUAL to do the comparison because it has
    -- JSON knowledge
    DBMS_OUTPUT.PUT_LINE('DB= ' || v_clob);
    DECLARE
      v_dummy VARCHAR2(1);
    BEGIN
      SELECT NULL
        INTO v_dummy
        FROM DUAL
       WHERE JSON_EQUAL(v_gen.gen_json,v_clob);
      DBMS_OUTPUT.PUT_LINE('They are equal');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('They are not equal');
    END;
  END LOOP;
END;
And the results:

-- GEN={"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}
-- DB= {"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}
-- They are equal
-- GEN={"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}
---DB= {"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}
-- They are equal

JSON Aware Comparison

JSON_EQUAL can handle attributes that are the same but in a different order. For example I’ll reverse the attribute order for Patient 1.

UPDATE json_patient
SET patient_data = '{"mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}],"pname":"Patient 1"}'
WHERE JSON_VALUE(patient_data,'$.pname') = 'Patient 1';

The comparison still returns TRUE!

-- GEN={"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}
-- DB= {"mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}],"pname":"Patient 1"}
-- They are equal
-- GEN={"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}
-- DB= {"pname":"Patient 2","mrns":[{"OS":1,"MRN":"MRN99"}]}
-- They are equal

Thanks for reading!

Leave a Comment