Contents

• pg_jsonschema
  • Summary
  • API
  • Usage
  • JSON Schema Support
  • Try it Out
  • Installation
  • json_matches_schema
  • 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 sql -- Validates a json *instance* against a *schema* json_matches_schema(schema json, instance json) returns bool and sql -- Validates a jsonb *instance* against a *schema* jsonb_matches_schema(schema json, instance jsonb) returns bool and sql -- Validates whether a json *schema* is valid jsonschema_is_valid(schema json) returns bool and sql -- 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: ```sql 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 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 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.