JSON Schema 2

Dataguides provide a map of JSON data and facilitate quick access to the elements contained within. I’ve demonstrated that in several other posts. This post expands on those concepts to show how Dataguides can be used to validate JSON as well.

What do I mean by validate? Good question – I mean ensuring that JSON data contains a set of specific elements in a specific order before being allowed into the database.

I’ll use a PL/SQL package to hold the “correct” Data Guide. Then I’ll use a database trigger to validate the inserted JSON against what is in the package. If the Data Guides don’t match then the insert is refused.

--Create a table with a JSON column
CREATE TABLE json_data
( json_id NUMBER NOT NULL PRIMARY KEY,
  json_col CLOB,
CONSTRAINT must_be_json
CHECK ( json_col IS JSON ));

-- Insert a row of JSON data
INSERT INTO json_data
VALUES(1,'{"KeyOne":"ValueOne"}');

-- View the Data Guide of the row inserted
SELECT JSON_DATAGUIDE(json_col)
  FROM json_data;
JSON_DATAGUIDE(JSON_COL)
----------------------------------------------------
[{"o:path":"$.KeyOne","type":"string","o:length":8}]

-- Create a package containing the Dataguide to
-- validate against. For the sake of simplicity I will
-- use the one shown above
CREATE OR REPLACE PACKAGE json_schema AS

  v_json_data VARCHAR2(100) := 
   '[{"o:path":"$.KeyOne","type":"string","o:length":8}]';

END;
/

-- And now the trigger that fires every time a record
-- is inserted
CREATE OR REPLACE TRIGGER json_schema
BEFORE INSERT OR UPDATE ON json_data
FOR EACH ROW
DECLARE
  v_dg CLOB;
BEGIN
  -- get the Data Guide of the new row
  SELECT JSON_DATAGUIDE(:NEW.json_col)
    INTO v_dg
    FROM DUAL;
  -- if the Data Guide is not a match then fail
  IF v_dg <> json_schema.v_json_data THEN
    RAISE_APPLICATION_ERROR(-20000,
                     'Incorrect JSON Format');
  END IF;
END;
/

-- Insert a record with the correct Data Guide
INSERT INTO json_data
VALUES(2,'{"KeyOne":"ValueOne"}');

-- Insert a record with an incorrect Data Guide
-- this will fail
INSERT INTO json_data
VALUES(3,'{"NotKeyOne":"NotValueOne"}');
INSERT INTO json_data
*
ERROR at line 1:
ORA-20000: Incorrect JSON Format
ORA-06512: at "D.JSON_SCHEMA", line 8
ORA-04088: error during execution of trigger
           'D.JSON_SCHEMA'

This is a simple way to validate JSON data before it is allowed into the database.

Thanks for reading!