# pg_jsonschema: JSON Schema Validation

Validate json/jsonb with JSON Schema in Postgres.

[JSON Schema](https://json-schema.org) is a language for annotating and validating JSON documents. [`pg_jsonschema`](https://github.com/supabase/pg_jsonschema) is a Postgres extension that adds the ability to validate Postgres's built-in `json` and `jsonb` data types against JSON Schema documents.

## Enable the extension

1. Go to the [Database](/dashboard/project/_/database/tables) page in the Dashboard.
2. Click on **Extensions** in the sidebar.
3. Search for `pg_jsonschema` and enable the extension.

```sql
-- Enable the "pg_jsonschema" extension
create extension pg_jsonschema with schema extensions;

-- Disable the "pg_jsonschema" extension
drop extension if exists pg_jsonschema;
```

Even though the SQL code is `create extension`, this is the equivalent of enabling the extension.
To disable an extension you can call `drop extension`.

It's good practice to create the extension within a separate schema (like `extensions`) to keep the `public` schema clean.

## Functions

- [`json_matches_schema(schema json, instance json)`](https://github.com/supabase/pg_jsonschema#api): Checks if a `json` _instance_ conforms to a JSON Schema _schema_.
- [`jsonb_matches_schema(schema json, instance jsonb)`](https://github.com/supabase/pg_jsonschema#api): Checks if a `jsonb` _instance_ conforms to a JSON Schema _schema_.

## Usage

Since `pg_jsonschema` exposes its utilities as functions, we can execute them with a select statement:

```sql
select
  extensions.json_matches_schema(
    schema := '{"type": "object"}',
    instance := '{}'
  );
```

`pg_jsonschema` is generally used in tandem with a [check constraint](https://www.postgresql.org/docs/current/ddl-constraints.html) as a way to constrain the contents of a json/b column to match a JSON Schema.

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

## Resources

- Official [`pg_jsonschema` documentation](https://github.com/supabase/pg_jsonschema)