“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!

 

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!

PLSQL and JSON and Recursion II

The previous post in this series demonstrated recursion to parse JSON data in PLSQL. It assumed the JSON passed in would start with key-value pairs. But JSON is not always that predictable. For example an array of key-value pairs could be passed in. Lets see what changes that requires.

First I’ll add 2 new rows to the Waldo table. Each new row contains an array of key-value pairs. Note the square brackets around the JSON data. This denotes an array.

After creating the data I’ll run the parsing function.

INSERT INTO waldo
VALUES(4,'[{"Waldo":"Elem1"},{"A":"B"},{"B":"C"}]');

INSERT INTO waldo
VALUES(5,'[{"A":"B"},{"Waldo":"Elem2"},{"B":"C"}]');

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
 find_waldo_key(waldo_json) waldo
 *
 ERROR at line 2:
 ORA-40587: invalid JSON type
 ORA-06512: at "SYS.JSON_OBJECT_T", line 90
 ORA-06512: at "D.FIND_WALDO_KEY", line 13

And here is unlucky line 13…

 v_object := JSON_OBJECT_T.PARSE(p_json);

It tries to use JSON_OBJECT_T but it fails because JSON_OBJECT_T insists data be in key-value pairs. It would work fine if the data was this:

{"Waldo":"Elem1"},{"A":"B"},{"B":"C"}

But we added square brackets to make it an array.

So what do we change in the code? I’m glad you asked. The main change is to parse the input as JSON_ELEMENT_T because it is more forgiving and then interrogate the element to see what it is.

CREATE OR REPLACE FUNCTION find_waldo_key (p_json VARCHAR2)
                  RETURN VARCHAR2 IS

 v_object  JSON_OBJECT_T;
 v_waldo   VARCHAR2(100);
 v_ret_val VARCHAR2(100);
 v_keys    JSON_KEY_LIST;
 v_type    VARCHAR2(100);
 v_x       VARCHAR2(100);
 v_element JSON_ELEMENT_T;

BEGIN

 -- load up an element because it is not picky
 -- it will take anything
 v_element := JSON_ELEMENT_T.PARSE(p_json);

 -- if the element is a JSON object then load
 -- it as an object and parse it later
 IF v_element.IS_OBJECT THEN
   v_object := JSON_OBJECT_T.PARSE(p_json);

 -- if the element is an array then loop through
 -- it's elements and call the parsing function again
 ELSIF v_element.IS_ARRAY THEN

   DECLARE
     v_array  JSON_ARRAY_T;
     v_size   NUMBER;
     v_e      JSON_ELEMENT_T;
     v_string VARCHAR2(100);
 BEGIN
     v_array := JSON_ARRAY_T.PARSE(v_element.to_string);
     v_size := v_array.get_size;
     FOR counter IN 0..(v_size - 1) LOOP
       v_element := v_array.get(counter);
       v_string := find_waldo_key(v_element.to_string);
       IF v_string <> 'No Waldo Here' THEN
         RETURN(v_string);
       END IF;
    END LOOP;
 END;

ELSE

 -- Just in case we get here and need
 -- to add more handling later
 RETURN('What is it?');

END IF;

-- To simplify the demo I'm going to hardcode
-- processing to the first key
v_keys := v_object.get_keys;
v_type := v_object.get_type(v_keys(1));

-- if the element is a scalar...
IF v_type = 'SCALAR' THEN

  -- if the object contains a key named Waldo
  -- then return its value
  IF v_object.has('Waldo') THEN
    v_waldo := v_object.get_string('Waldo');
    v_ret_val := v_waldo;
  ELSE
    v_ret_val := 'No Waldo Here';
  END IF;

-- if the element is an object then send it to the
-- parse function
ELSIF v_type = 'OBJECT' THEN

  v_object := v_object.get_object(v_keys(1));
  v_x := find_waldo_key(v_object.TO_CLOB);
  v_ret_val := v_x;

END IF;

RETURN(v_ret_val);

END;

And here are the results (including rows from the previous post).

SELECT waldo_id,
       find_waldo_key(waldo_json) waldo
  FROM waldo;
 
WALDO_ID   WALDO
---------- ------------------------------
         1 No Waldo Here
         2 Top
         3 First Peep
         4 Elem1
         5 Elem2

That’s a lot of code. And it only handles a fraction of whats possible in JSON data. I fear that adding functionality to it will make things more complex than it’s worth.

What I really want is a way to ask the JSON what it’s structure is and then navigate through it using that information as a … wait for it … Data Guide.

In the next few posts I’ll show how to use Oracle Data Guide to make parsing much easier.

Thanks for reading!