PEIM’s Are Strict

JSON Path Expression Item Method’s (PEIM) were added in Oracle 18c. They provide extensions to interpret JSON data. But the interpretations are stricter than they appear. Take the string() PEIM for instance – the Oracle documentation says it returns an “SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value”. That seems straightforward but…

-- Create a row of JSON data
INSERT INTO json_data
VALUES(1,'{"RESTAURANT":"A_AND_W",
           "ROOT_BEER":"FREE",
           "DAYS":1}');

-- Query row into matching variable types (NUMBER
-- and VARCHAR2 using the JSON_TABLE function
SELECT jd.*
  FROM json_data,
       JSON_TABLE(json_col,'$'
COLUMNS ( RESTAURANT VARCHAR2(30) PATH '$.RESTAURANT',
          ROOT_BEER  VARCHAR2(30) PATH '$.ROOT_BEER',
          DAYS       NUMBER       PATH '$.DAYS')) jd;

RESTAURANT ROOT_BEER DAYS
---------- --------- ----
A_AND_W    FREE         1

Now I’ll introduce the string() PEIM to modify the numeric DAYS value to a string.

SELECT jd.*
FROM json_data,
JSON_TABLE(json_col,'$'
COLUMNS ( RESTAURANT VARCHAR2(30) PATH '$.RESTAURANT',
          ROOT_BEER  VARCHAR2(30) PATH '$.ROOT_BEER',
       DAYS  NUMBER    PATH '$.DAYS.string()')) jd;

This raises the error ORA-40484: invalid data type for
                                 JSON_TABLE column

The string()  interpretation is very strict. It wont even allow a numeric value to be considered a string. It determines its an invalid datatype and throws the 40484 exception.

Can that be trapped with ON ERROR clauses?

SELECT jd.*
  FROM json_data,
       JSON_TABLE(json_col,'$' NULL ON ERROR
COLUMNS ( RESTAURANT VARCHAR2(30) PATH '$.RESTAURANT',
          ROOT_BEER  VARCHAR2(30) PATH '$.ROOT_BEER',
 DAYS NUMBER PATH '$.DAYS.string()' NULL ON ERROR )) jd;

Nope. This error flies right past it.

The solution is to change the return type needs to change…

SELECT jd.*
  FROM json_data,
       JSON_TABLE(json_col,'$'
COLUMNS ( RESTAURANT VARCHAR2(30) PATH '$.RESTAURANT',
          ROOT_BEER  VARCHAR2(30) PATH '$.ROOT_BEER',
DAYS       VARCHAR2(1)  PATH '$.DAYS.string()')) jd;

RESTAURANT ROOT_BEER DAYS
---------- --------- ----
A_AND_W    FREE         1

So beware! The word interpretation may seem innocuous but its very strict.

Thanks for reading.