Database

pg_jsonschema: JSON Schema Validation

JSON Schema is a language for annotating and validating JSON documents. pg_jsonschema is a PostgreSQL extension that adds the ability to validate PostgreSQL's built-in json and jsonb data types against JSON Schema documents.

Enable the extension

Functions

Usage

Since pg_jsonschema exposes its utilities as functions, we can execute them with a select statement:


_10
select
_10
extensions.json_matches_schema(
_10
schema := '{"type": "object"}',
_10
instance := '{}'
_10
);

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.


_36
create table customer(
_36
id serial primary key,
_36
...
_36
metadata json,
_36
_36
check (
_36
json_matches_schema(
_36
'{
_36
"type": "object",
_36
"properties": {
_36
"tags": {
_36
"type": "array",
_36
"items": {
_36
"type": "string",
_36
"maxLength": 16
_36
}
_36
}
_36
}
_36
}',
_36
metadata
_36
)
_36
)
_36
);
_36
_36
-- Example: Valid Payload
_36
insert into customer(metadata)
_36
values ('{"tags": ["vip", "darkmode-ui"]}');
_36
-- Result:
_36
-- INSERT 0 1
_36
_36
-- Example: Invalid Payload
_36
insert into customer(metadata)
_36
values ('{"tags": [1, 3]}');
_36
-- Result:
_36
-- ERROR: new row for relation "customer" violates check constraint "customer_metadata_check"
_36
-- DETAIL: Failing row contains (2, {"tags": [1, 3]}).

Resources