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