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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for pg_jsonschema and 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