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, there 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 the whole thing in a function that will then simply return a boolean value for the JSON passed into it.

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;
/