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!