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!

Oracle Prequel

April 1, 2018

Oracle Spokesperson April Fulz released the following brief statement today:

The Oracle database has undergone many changes to it’s release numbering scheme over the years that caused an undue amount of confusion. To alleviate the confusion we have decided to go right back to the beginning with Release 1.0.

You may know that we skipped release 1.0 in the early days for fear that nobody would trust the 1.0 release of any software. This was just the first rule of our release numbering scheme. Other highlights include:

  • Using incremental whole numbers for each release to denote forward movement and added features
  • Offset the intimidation of whole number increments by including decimal points
  • Append decrimental lowercase letters (i, g, c) to further offset the perceived complexity (thankfully industry trends such as Internet [i] and Cloud [c] arose to make the chosen letters more relevant)
  • The years between 13 and 17 were awkward for a lot of us so we planned to skip those release numbers and go directly to 18 (thankfully the year 2018 arrived just in time for this)
  • Just like the great movie franchises we always planned a prequel release of our flagship Database that we are now proudly branding Oracle RDBMS 1.0

More information will be made available closer to the final release of…the first release.

“JSON Schema” One

During the February 2018 ODTUG Code Talk about JSON and PLSQL an attendee asked the following question:

Can we validate JSON using JSON-schema?
(similar to XML-schema)?

I did not have enough information at the time to answer the question so I did some research afterwards and found that XML schema’s have three basic requirements:

1. Provide a list of elements XML contains
2. Provide documentation for humans and (XML) machines
3. Constrain data input to match the specified elements

I don’t know is there is such this as JSON Schema but Oracle satisfies the first two requirements easily with JSON Data Guides. Below is a explanation using code:

-- 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 record with a single JSON key-value pair
INSERT INTO json_data
VALUES(1,'{"KeyOne":"ValueOne"}');

-- Query the dataguide for the table using:
-- 1) JSON_DATAGUIDE function to extract element info:
--    o:path = Path to the element from the JSON top
--    type = the data type of the element
--    length = the length of the element
-- 2) JSON_TABLE to translate JSON element into columns
--    and rows
WITH dataguide_view AS
  ( SELECT JSON_DATAGUIDE(json_col) dataguide_rows
FROM json_data
)
SELECT edetails.*
  FROM dataguide_view,
       JSON_TABLE(dataguide_rows,'$[*]'
    COLUMNS epath VARCHAR2(100) PATH '$."o:path"',
            etype VARCHAR2(10) PATH '$."type"',
            elength NUMBER PATH '$."o:length"') edetails;

EPATH     ETYPE   ELENGTH
--------- ------- -------
$.KeyOne  string        8

-- Now lets add some more complex elements such as arrays
INSERT INTO json_data
VALUES(2,'{"KeyOne":"ValueOne",
           "KeyTwo":"ValueTwo",
           "KeyThree":{"K1":"V1","K2":"V2"},
           "KeyFour":[1,2,3,4,{"K4":"V4"}]}');
 1 row created.

-- Running the dataguide query again shows this result:
EPATH         ETYPE   ELENGTH
------------- ------- ------- 
$.KeyOne      string        8
$.KeyTwo      string        8
$.KeyFour     array        32
$.KeyFour.K4  string        2
$.KeyThree    object       32
$.KeyThree.K1 string        2
$.KeyThree.K2 string        2

This output is for every element in any record in the table. Data Guide does not inherently constrain JSON data – whenever a new element is added or removed the Data Guide simply updates.

In the next post I’ll demonstrate using Data Guide to satisfy the third requirement – “Constrain data input to match the specified elements”.

Thanks for reading!