Validating JSON in the database

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

[source language=”sql”]
create table json_validator (
id number generated always as identity primary key
,json clob
,constraint validate_json check (json is json format json)
);

create or replace function validate_json (l_json in clob)
return boolean
is
l_id pls_integer;
e_invalid_json exception;
pragma exception_init (e_invalid_json,-2290);
pragma autonomous_transaction;
begin
insert into json_validator (json) values (l_json);
rollback;
return true;
exception
when e_invalid_json then
return false;
end;
/

set serveroutput on
declare
l_json clob;
l_return boolean;
begin
l_json := ‘
{"widget": {
"debug": "on",
"window": {
"title": "sample konfabulator widget",
"name": "main_window",
"width": 500,
"height": 500
},
"image": {
"src": "images/sun.png",
"name": "sun1",
"hoffset": 250,
"voffset": 250,
"alignment": "center"
},
"text": {
"data": "click here",
"size": 36,
"style": "bold",
"name": "text1",
"hoffset": 250,
"voffset": 100,
"alignment": "center",
"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"
}
}}
‘;

l_return := validate_json(l_json => l_json );

if l_return then
dbms_output.put_line(‘JSON is valid’);
else
dbms_output.put_line(‘JSON is NOT valid’);
end if;

end;
/

[/source]

3 thoughts on “Validating JSON in the database

  • April 24, 2017 at 7:22 pm
    Permalink

    Couple ideas; You could APEX_JSON.PARSE
    Or inline sub select the JSON to avoid the validator table

    Reply
  • August 25, 2017 at 5:39 am
    Permalink

    will this code works in11g 4 db??

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *