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.

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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.