jsonpostgresqljsonschemapostgresql-json

JSON Schema validation in PostgreSQL?

I can't find any information about JSON schema validation in PostgreSQL, is there any way to implement JSON Schema validation on PostgreSQL JSON data type?

Solution

  • There is a PostgreSQL extension that implements JSON Schema validation in PL/PgSQL.

    It is used like this (taken from the project README file):

    CREATE TABLE example (id serial PRIMARY KEY, data jsonb);
ALTER TABLE example ADD CONSTRAINT data_is_valid CHECK (validate_json_schema('{"type": "object"}', data));

INSERT INTO example (data) VALUES ('{}');
-- INSERT 0 1

INSERT INTO example (data) VALUES ('1');
-- ERROR:  new row for relation "example" violates check constraint "data_is_valid"
-- DETAIL:  Failing row contains (2, 1).