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!

Leave a Comment Cancel reply