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.

JSON Data Guide Nuance 3

Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in the array. That changed in version 18 of Oracle as shown below.

-- Create and populate a table
CREATE TABLE json_data(
    json_pk NUMBER NOT NULL PRIMARY KEY,
    json_col CLOB
    CONSTRAINT is_json CHECK ( json_col IS JSON ) );

INSERT INTO json_data
VALUES(1,'{"NUMBERS":[1,2,3,4,5],
           "STRINGS":["A","B","C"],
           "MIXED":[1,2,3,"A","B"]}');
COMMIT;

WITH dataguide_view AS
( SELECT json_pk,
         JSON_DATAGUIDE(json_col) dataguide_rows
    FROM json_data
  GROUP BY json_pk)
SELECT json_pk,
       edetails.*
  FROM dataguide_view,
       JSON_TABLE(dataguide_rows,'$[*]'
 COLUMNS epath VARCHAR2(100) PATH '$."o:path"',
         etype VARCHAR2(10) PATH '$."type"',
         elength NUMBER PATH '$."o:length"') edetails;

-- Result in 12c
-- All we know is there is an array of something
JSON_PK EPATH      ETYPE  ELENGTH
------- ---------- ------ -------
      1 $.MIXED    array       16
      1 $.NUMBERS  array       16
      1 $.STRINGS  array       16

-- Result in 18c
-- The datatype of the scalar entries is provided
JSON_PK EPATH        ETYPE  ELENGTH
------- ------------ ------ -------
      1 $.MIXED      array       16
      1 $.MIXED[*]   string       1
      1 $.NUMBERS    array       16
      1 $.NUMBERS[*] number       1
      1 $.STRINGS    array       16
      1 $.STRINGS[*] string       1

Thanks for reading!

JSON Data Guide Nuance 2

The previous post used numeric and string value to demonstrate how Oracle JSON Data Guide may change assigned datatypes to accommodate mixed data. This post demonstrates the same concept using JSON boolean values.

SQL> INSERT INTO json_data
  2  VALUES(1,'{"RAPTOR_LOST":true}');
1 row created.

SQL> INSERT INTO json_data
  2  VALUES(2,'{"RAPTOR_LOST":"YES"}');
1 row created.

SQL> SELECT JSON_DATAGUIDE(json_col) boolean
  2    FROM json_data
  3  GROUP BY json_pk;
BOOLEAN
----------------------------------------------------------
[{"o:path":"$.RAPTOR_LOST","type":"boolean","o:length":4}]
[{"o:path":"$.RAPTOR_LOST","type":"string","o:length":4}]

SQL> SELECT JSON_DATAGUIDE(json_col) boolean
  2    FROM json_data;
BOOLEAN
---------------------------------------------------------
[{"o:path":"$.RAPTOR_LOST","type":"string","o:length":4}]

Be careful to allow for these changes in your code.

Thanks for reading!