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

Two SQL functions:

• json_matches_schema
• jsonb_matches_schema (note the jsonb in front)

With the following signatures

-- 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
                        }
                    }
                }
            }'::json,
            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:

• pgrx

cargo pgrx 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 pgrx 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 pgrx + 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.