Contents

• pg_jsonschema
  • Summary
  • API
  • Usage
  • JSON Schema Support
  • Try it Out
  • Installation
  • Releasing
    • Idempotency
    • Individual Scripts
  • Prior Art
  • Benchmark
    • Setup

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

This extension exposes the following four SQL functions:

• json_matches_schema
• jsonb_matches_schema (note the jsonb in front)
• jsonschema_is_valid
• jsonschema_validation_errors

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

and

-- Validates whether a json *schema* is valid
jsonschema_is_valid(schema json) returns bool

and

-- Returns an array of errors if a *schema* is invalid
jsonschema_validation_errors(schema json, instance json) returns text[]

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]}).

-- Example: jsonschema_validation_errors
select jsonschema_validation_errors('{"maxLength": 4}', '"123456789"');
-- Result:
--   ERROR: "123456789" is longer than 4 characters

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.

Releasing

Releases are automated via a single command:

./scripts/release.sh <major.minor.patch>

For example:

./scripts/release.sh 0.4.0

This orchestrates the full release process end-to-end:

1. Verifies that the tag and GitHub release don’t already exist
2. Updates versions in Cargo.toml, META.json, and Cargo.lock; creates a release/<version> branch; commits, pushes, and waits for the PR to be merged into master
3. Verifies all file versions match before tagging
4. Creates and pushes the v<version> tag, which triggers the CI workflows (release.yml for GitHub release + .deb artifacts, pgxn-release.yml for PGXN)
5. Polls GitHub until the release is published and prints the release URL

Note: pg_jsonschema.control uses @CARGO_VERSION@ which is substituted by pgrx at build time from Cargo.toml, so it doesn’t need manual updates.

Idempotency

The script is safe to re-run if interrupted — it detects what has already been completed (branch exists, tag exists, release exists) and picks up where it left off.

Individual Scripts

The release process is composed of smaller scripts that can also be run independently:

Script	Purpose
scripts/check-version.sh <version>	Checks if Cargo.toml and META.json match the given version
scripts/update-version.sh <version>	Updates version files, creates a release branch, and waits for PR merge
scripts/update-version.sh --files-only <version>	Updates version files without any git operations
scripts/push-tag.sh <version>	Creates and pushes the git tag, then monitors for the GitHub release
scripts/push-tag.sh --dry-run <version>	Validates versions without creating a tag

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 14.2
• PostgreSQL 16.2

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, 20000) t(i);
-- Query Completed in 351 ms

for comparison, the equivalent test using postgres-json-schema’s validate_json_schema function ran in 5.54 seconds. pg_jsonschema’s ~15x speedup on this example JSON schema grows quickly as the schema becomes more complex.