Validating And Constraining JSON

My February ODTUG Code Talk about Oracle PLSQL and JSON spawned many great questions from the attendees. I’ll answer them in this series of posts.

The first two of questions asked were:

  1. can you validate data being stored
  2. Can you create constraints?

I’ll answer them both below.

First up is an example of validating JSON using an index with the “ERROR ON ERROR” clause to check if JSON contains a specific key. The explanation and code for that is here.

And now for some code!

The code below demonstrates unique key constraints and value checking. It also shows triggers for validating and constraining JSON.

--
-- The WITH UNIQUE KEYS clause stipulates that
-- duplicate KEYS cannot exist at the same level in
-- the JSON data. Please note this refers to KEY
-- name and NOT the VALUE of a KEY:VALUE pair.
-- Also note it only check within the "current"
-- set of JSON data.
--
CREATE TABLE json_data
( json_id NUMBER NOT NULL PRIMARY KEY,
  json_col CLOB,
CONSTRAINT uniqueness_reqd
CHECK ( json_col IS JSON WITH UNIQUE KEYS ));

-- This row has unique KEYS of KEY1 and KEY2
INSERT INTO json_data
VALUES(1,'{"KEY1":"VAL1",
           "KEY2":"VAL2"}');

-- This row does not have unique keys at the top
-- level so it cannot be inserted
INSERT INTO json_data
VALUES(2,'{"KEY1":"VAL1",
           "KEY1":"VAL77"}');
INSERT INTO json_data
*
ERROR at line 1:
ORA-02290: check constraint (D.UNIQUENESS_REQD)
           violated
--
-- Unique indexes can be created to ensure that the
-- VALUE side of a KEY:VALUE pair is unique. In this
-- example the KEY name is KEY1. Unique indexes apply
-- across ALL records in the table.
--
CREATE UNIQUE INDEX unique_val1 ON
   json_data jd ( JSON_VALUE(jd.json_col,'$.KEY1'));
Index created.

-- Query what rows exist in the table
SELECT JSON_VALUE(jd.json_col,'$.KEY1') jv
  FROM json_data jd;
JV
--------------------
VAL1

-- Try to insert a value of VAL1 for KEY1 which
-- violates the unique index
INSERT INTO json_data
VALUES(3,'{"KEY1":"VAL1"}');
INSERT INTO json_data
*
ERROR at line 1:
ORA-00001: unique constraint (D.UNIQUE_VAL1) violated

-- This insert of VAL11 (eleven) succeeds
INSERT INTO json_data
VALUES(4,'{"KEY1":"VAL11"}');
1 row created.

-- Triggers can also be created to validate data
CREATE OR REPLACE TRIGGER json_trigger
   AFTER INSERT ON json_data
   FOR EACH ROW
BEGIN
  IF JSON_VALUE(:NEW.json_col,'$.KEY1') = 'BAD' THEN
    RAISE_APPLICATION_ERROR(-20000,'Bad JSON');
  END IF;
END;
/
Trigger created.

-- The trigger fires an prevents this insert based
-- on the VALUE for the KEY1 key
INSERT INTO json_data
VALUES(5,'{"KEY1":"BAD"}');
INSERT INTO json_data
 *
ERROR at line 1:
ORA-20000: Bad JSON
ORA-06512: at "D.JSON_TRIGGER", line 3
ORA-04088: error during execution of
                      trigger 'D.JSON_TRIGGER'

-- This insert succeeds
INSERT INTO json_data
VALUES(6,'{"KEY1":"GOOD"}');
1 row created.

-- Now query whats in the table.
SELECT JSON_VALUE(jd.json_col,'$.KEY1') jv
  FROM json_data jd;
JV
--------------------
VAL1
VAL11
GOOD

There is one more way to validate JSON. You can specify a procedure to run when the Data Guide for the JSON data changes. Beda Hammerschmidt of Oracle shows a demo of this.

Thanks for reading!