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.

SQL functions:

-- Validates a json *instance* against a *schema*
json_matches_schema(schema json, instance json) returns bool

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

Installation

Requires:

• pgx

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 - an implementation of JSON Schema for Postgres written in PL/pgSQL

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.