# pg_jsonschema

<p>
<a href=""><img src="https://img.shields.io/badge/postgresql-12+-blue.svg" alt="PostgreSQL version" height="18"></a>
<a href="https://github.com/supabase/pg_jsonschema/blob/master/LICENSE"><img src="https://img.shields.io/pypi/l/markdown-subtemplate.svg" alt="License" height="18"></a>

</p>

---

**Source Code**: <a href="https://github.com/supabase/pg_jsonschema" target="_blank">https://github.com/supabase/pg_jsonschema</a>

---

## Summary

`pg_jsonschema` is a PostgreSQL extension adding support for [JSON schema](https://json-schema.org/) 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](https://docs.rs/jsonschema/latest/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](https://github.com/tcdi/pgrx)


```shell
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](https://github.com/tcdi/pgrx) docs.


## Prior Art

[postgres-json-schema](https://github.com/gavinwahl/postgres-json-schema) - JSON Schema Postgres extension written in PL/pgSQL

[is_jsonb_valid](https://github.com/furstenheim/is_jsonb_valid) - JSON Schema Postgres extension written in C

[pgx_json_schema](https://github.com/jefbarn/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

```json
{
    "type": "object",
    "properties": {
        "a": {"type": "number"},
        "b": {"type": "string"}
    }
}
```

```sql
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.