pg_jsonschema: JSON Schema Validation
JSON Schema is a language for annotating and validating JSON documents. pg_jsonschema
is a Postgres extension that adds the ability to validate PostgreSQL's built-in json
and jsonb
data types against JSON Schema documents.
Enable the extension
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for
pg_jsonschema
and enable the extension.
Functions
json_matches_schema(schema json, instance json)
: Checks if ajson
instance conforms to a JSON Schema schema.jsonb_matches_schema(schema json, instance jsonb)
: Checks if ajsonb
instance conforms to a JSON Schema schema.
Usage
Since pg_jsonschema
exposes its utilities as functions, we can execute them with a select statement:
12345select extensions.json_matches_schema( schema := '{"type": "object"}', instance := '{}' );
pg_jsonschema
is generally used in tandem with a check constraint as a way to constrain the contents of a json/b column to match a JSON Schema.
123456789101112131415161718192021222324252627282930313233343536create table customer( id serial primary key, ... metadata json, check ( json_matches_schema( '{ "type": "object", "properties": { "tags": { "type": "array", "items": { "type": "string", "maxLength": 16 } } } }', metadata ) ));-- Example: Valid Payloadinsert into customer(metadata)values ('{"tags": ["vip", "darkmode-ui"]}');-- Result:-- INSERT 0 1-- Example: Invalid Payloadinsert into customer(metadata)values ('{"tags": [1, 3]}');-- Result:-- ERROR: new row for relation "customer" violates check constraint "customer_metadata_check"-- DETAIL: Failing row contains (2, {"tags": [1, 3]}).
Resources
- Official
pg_jsonschema
documentation