2021 – JSON Plus Mismatch Types = More Flexibility

by

The previous post introduced the ON MISMATCH for loading mismatched data into an Oracle Object. This post expands on that by explaining the three specific types of mismatches. They are:

  • MISSING DATA
    • The JSON does not have all of the keys to match the object type
  • EXTRA DATA
    • The JSON has more keys than the object type
  • TYPE ERROR
    • One or more of the data types between JSON keys and object type structures do not match. For example a string JSON value into a numeric object column

If none of the types are specified then all 3 are assumed.

Here are some demos. First is the test data.

SELECT *
  FROM json_patient;
PATIENT_DATA
--------------------------------------------------------------------------------
{"pname" : "Patient 1","mrns" : [{"OS" : 1,"MRN" : "1"},{"OS" : 2,"MRN" : "2"}]}
{"pname" : "Patient 2","mrns" : [{"OS" : 1,"MRN" : "3"},{"OS" : 2,"MRN" : "4"}]}

And the mrn_date_o object definition.

CREATE OR REPLACE TYPE mrn_date_o AS OBJECT ( os    NUMBER,
                                               mrn   VARCHAR2(100),
                                               sdate DATE,
                                               edate DATE);

Note – the objects in the mrns JSON array have 2 keys (OS and MRN) while the object has 4 fields (OS, MRN, SDATE, and EDATE).

Did you just use a note to foreshadow something? Yes I did! Thanks for noticing.

And code that specifies all 3 mismatch types.

BEGIN
   FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
              RETURNING mrn_date_o
              ERROR ON MISMATCH( MISSING DATA,
                                 EXTRA DATA,
                                 TYPE ERROR ) ) mrn_date_o
                FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

Raises ORA-40601: missing data for object type conversion ORA-06512: at line 2

This next example will not raise the error because MISSING DATA is not specified.

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
                    RETURNING mrn_date_o
                    ERROR ON MISMATCH( EXTRA DATA,
                                       TYPE ERROR ) ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

You can mix and match…or mix and mismatch…

I see what you did there. Very witty…
BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
                    RETURNING mrn_date_o
                    ERROR ON MISMATCH( EXTRA DATA,
                                       TYPE ERROR )
                    IGNORE ON MISMATCH ( MISSING DATA ) ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;

But only 1 per type.

BEGIN
  FOR x IN ( SELECT JSON_VALUE(patient_data,'$.mrns[0]'
                    RETURNING mrn_date_o
                    ERROR ON MISMATCH( EXTRA DATA,
                                       TYPE ERROR )
                    IGNORE ON MISMATCH ( TYPE ERROR ) ) mrn_date_o
               FROM json_patient ) LOOP
    DBMS_OUTPUT.PUT_LINE('MRN ' || x.mrn_date_o.os || ' ' || x.mrn_date_o.mrn);
    DBMS_OUTPUT.PUT_LINE('DT ' || x.mrn_date_o.sdate || ' ' || x.mrn_date_o.edate);
  END LOOP;
END;
Raises -- PL/SQL: ORA-40450: invalid ON ERROR clause

The next post will delve deeper into what can (and cannot) be done with JSON and Object Types.

Thanks for reading!

Leave a Comment