The 2020 Project

Way back in the previous decade I asked some PL/SQL developers how they might use Polymorphic Table Functions (PTF) and JSON Dataguides. Almost all of the replies involved translating JSON keys and values into columns and rows on the fly. So with that and a brand new decade in mind I’m embarking on a new series called the 2020 Project.

In a nutshell I’ll be building a PTF to go from this JSON data stored in an Oracle DB.

SELECT *
  FROM json_stuff;

JSON_ID    JSON_DATA
------- ---------------------------------
      1 {"KEY1":"VALUE1"}
      2 {"KEY2":"VALUE2","KEY3":"VALUE3"}

To displaying the keys and values as columns and values like this:

SELECT key1,
       key2,
       key3
  FROM add_dataguide_func(json_stuff);

KEY1   KEY2   KEY3
------ ------ ------
VALUE1
       VALUE2 VALUE3

Over the next few months I’ll assemble and explain code to perform the translation. I’ll post the code on the Oracle livesql site so you can follow along. The introduction code snippets are available here.

NB1 If you just need to qucikly get translations done then have a look a Oracle’s Dataguide indexes and views created by the DBMS_JSON package.

NB2 If you are looking for an in depth tour of PTF’s and JSON Dataguides then I think you’ll enjoy this series.

Thanks for reading!

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!