pg_jsonschema: JSON Schema Validation

JSON Schema is a language for annotating and validating JSON documents. 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 page in the Dashboard.
2. Click on Extensions in the sidebar.
3. Search for pg_jsonschema and enable the extension.

Functions#

• json_matches_schema(schema json, instance json): Checks if a json instance conforms to a JSON Schema schema.
• jsonb_matches_schema(schema json, instance jsonb): 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:

1select2  extensions.json_matches_schema(3    schema := '{"type": "object"}',4    instance := '{}'5  );

pg_jsonschema is generally used in tandem with a check constraint as a way to constrain the contents of a json/b column to match a JSON Schema.

1create table customer(2    id serial primary key,3    ...4    metadata json,56    check (7        json_matches_schema(8            '{9                "type": "object",10                "properties": {11                    "tags": {12                        "type": "array",13                        "items": {14                            "type": "string",15                            "maxLength": 1616                        }17                    }18                }19            }',20            metadata21        )22    )23);2425-- Example: Valid Payload26insert into customer(metadata)27values ('{"tags": ["vip", "darkmode-ui"]}');28-- Result:29--   INSERT 0 13031-- Example: Invalid Payload32insert into customer(metadata)33values ('{"tags": [1, 3]}');34-- Result:35--   ERROR:  new row for relation "customer" violates check constraint "customer_metadata_check"36--   DETAIL:  Failing row contains (2, {"tags": [1, 3]}).

Resources#

• Official pg_jsonschema documentation