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:

select
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.

create 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 Payload
insert into customer(metadata)
values ('{"tags": ["vip", "darkmode-ui"]}');
-- Result:
-- INSERT 0 1

-- Example: Invalid Payload
insert 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#


We only collect analytics essential to ensuring smooth operation of our services.

Learn more