Skip to content

supabase/pg_jsonschema

master
Switch branches/tags
Code

Files

Permalink
Failed to load latest commit information.

pg_jsonschema

PostgreSQL version License


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.

About

PostgreSQL extension providing JSON Schema validation

Resources

License

Code of conduct

Stars

Watchers

Forks

Sponsor this project

 

Packages

No packages published