Posted on

JSON_OBJECT And FORMAT JSON

The Oracle PLSQL JSON_OBJECT function assembles JSON KEY:VALUE pairs. It uses column values from a table or view as the VALUE. It can use hardcoded strings or column values as the KEY.  Here is an example creating 3 records in a table and querying them using the JSON_OBJECT function.

-- record 1 NOT JSON
INSERT INTO demo VALUES('Not JSON At All');

-- record 2 JSON STRICT 
INSERT INTO demo VALUES('{"KEY":"VALUE"}'); 

-- record 3 JSON LAX
INSERT INTO demo VALUES('{KEY:VALUE}'); 

-- assemble JSON KEY:VALUE pair using harcoded 'JSON' as
-- the key and values from the json_text_col column as
-- the values 
-- JSON_OBJECT will add double quotes
SELECT JSON_OBJECT('JSON' VALUE json_text_col) AS JSON_OBJECT
  FROM demo;
JSON_OBJECT
----------------------------
{"JSON" : "Not JSON At All"}
{"JSON" : "{"KEY":"VALUE"}"}
{"JSON" : "{KEY:VALUE}"}

So, what do JSON STRICT and LAX mean? I’m glad you asked. The STRICT specification dictates that KEY and VALUE attributes must be enclosed in double quotes while JSON LAX does not require this.

The default for Oracle is LAX. Thus specifying JSON is equal to specifying JSON LAX.

Strict and lax come into play with JSON_OBJECT via the FORMAT JSON clause shown in this example. Note the lack of added double quotes added.

SELECT JSON_OBJECT('JSON' VALUE json_text_col FORMAT JSON)
          AS FORMAT_JSON FROM demo;
FORMAT_JSON
----------------------------
{"JSON":Not JSON At All}
{"JSON" : {"KEY" : "VALUE"}} 
{"JSON":{KEY:VALUE}}

The FORMAT JSON tells Oracle to not perform any formatting (quotes, etc) on the values from the json_text_col column. The double quotes on record 2 were already there.

The default for FORMAT JSON is LAX so FORMAT JSON is the same as FORMAT JSON LAX

So is there a FORMAT JSON STRICT?

Of course there is. But it has some nuances. Here’s an example:

SELECT JSON_OBJECT('JSON' VALUE json_text_col
          FORMAT JSON STRICT) AS JSON_STRICT
FROM demo;
ORA-40441: JSON syntax error

Because records 1 and 3 do not satisfy the JSON STRICT requirement the query fails out with the ORA-40441 error.

How can you get past that? Just add an IS JSON or IS JSON STRICT to the WHERE clause for the query.

SELECT JSON_OBJECT('JSON' VALUE json_text_col
         FORMAT JSON STRICT) AS JSON_STRICT_IS_JSON
  FROM demo
 WHERE json_text_col IS JSON;
JSON_STRICT_IS_JSON
----------------------------
{"JSON" : {"KEY" : "VALUE"}}

SELECT JSON_OBJECT('JSON' VALUE json_text_col
         FORMAT JSON STRICT) AS JSON_STRICT_IS_JSON_STRICT
  FROM demo
 WHERE json_text_col IS JSON STRICT;
JSON_STRICT_IS_JSON_STRICT
----------------------------
{"JSON" : {"KEY" : "VALUE"}}

Thanks for reading!

Posted on

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.

Posted on

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!