pg_jsonschema
Source Code: https://github.com/supabase/pg_jsonschema
Summary
pg_jsonschema
is a PostgreSQL extension adding support for JSON schema validation on json
and jsonb
data types.
API
SQL functions:
-- Validates a json *instance* against a *schema*
json_matches_schema(schema json, instance json) returns bool
and
-- Validates a jsonb *instance* against a *schema*
jsonb_matches_schema(schema json, instance jsonb) returns bool
Usage
Those functions can be used to constrain json
and jsonb
columns to conform to a schema.
For example:
create extension pg_jsonschema;
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]}).
JSON Schema Support
pg_jsonschema is a (very) thin wrapper around the jsonschema rust crate. Visit their docs for full details on which drafts of the JSON Schema spec are supported.
Try it Out
Spin up Postgres with pg_jsonschema installed in a docker container via docker-compose up
. The database is available at postgresql://postgres:password@localhost:5407/app
Installation
Requires:
cargo pgx run
which drops into a psql prompt.
psql (13.6)
Type "help" for help.
pg_jsonschema=# create extension pg_jsonschema;
CREATE EXTENSION
pg_jsonschema=# select json_matches_schema('{"type": "object"}', '{}');
json_matches_schema
---------------------
t
(1 row)
for more complete installation guidelines see the pgx docs.
Prior Art
postgres-json-schema - JSON Schema Postgres extension written in PL/pgSQL
is_jsonb_valid - JSON Schema Postgres extension written in C
pgx_json_schema - JSON Schema Postgres extension written with pgx + jsonschema
Benchmark
System
- 2021 MacBook Pro M1 Max (32GB)
- macOS 12.4
- PostgreSQL 14.1
Setup
Validating the following schema on 20k unique inserts
{
"type": "object",
"properties": {
"a": {"type": "number"},
"b": {"type": "string"}
}
}
create table bench_test_pg_jsonschema(
meta jsonb,
check (
jsonb_matches_schema(
'{"type": "object", "properties": {"a": {"type": "number"}, "b": {"type": "string"}}}',
meta
)
)
);
insert into bench_test_pg_jsonschema(meta)
select
json_build_object(
'a', i,
'b', i::text
)
from
generate_series(1, 200000) t(i);
-- Query Completed in 2.18 seconds
for comparison, the equivalent test using postgres-json-schema's validate_json_schema
function ran in 5.54 seconds. pg_jsonschema's ~2.5x speedup on this example JSON schema grows quickly as the schema becomes more complex.