JSON Check Constraints…or not

I’ve been researching the Oracle SQL JSON functions and found some weird interesting behaviors. My New Year’s resolution is to look for the goodness in things so I’ll present them as features and tie them loosely (very, very loosely) to existing Oracle features. Here’s the first entry.

The following code imitates a check constraint by requiring an element named MustBeThere.

-- Create a table with a JSON column
SQL> CREATE TABLE json_data
 2 ( json_col CLOB,
 3 CONSTRAINT json_or_nothing_pal
 4 CHECK ( json_col IS JSON ) );

Table created.

-- create a JSON_VALUE index on an element named MustBeThere
-- and specify ERROR ON ERROR to fail if the element is
-- not found
SQL> CREATE INDEX json_data_ix ON json_data jd
 2 ( JSON_VALUE(jd.json_col,'$.MustBeThere' ERROR ON ERROR ));

Index created.

-- Try to insert a record without the MustBeThere element
-- The index ERROR ON ERROR clause will cause it to fail
SQL> INSERT INTO json_data
 2 VALUES(
 3 '{"akey" : "avalue" }');
INSERT INTO json_data
 *
ERROR at line 1:
ORA-40462: JSON_VALUE evaluated to no value


-- Insert a record with the element
SQL> INSERT INTO json_data
 2 VALUES(
 3 '{"MustBeThere" : "OKHereItIs"}' );

1 row created.

I don’t recommend actually doing this because because the error message is not very helpful and will cause confusion. I just wanted to make everyone aware of the situation in case they encounter it.

Happy 2017 and thanks for reading!