pg_jsonschema: JSON Schema Validation

JSON Schema is a language for annotating and validating JSON documents. pg_jsonschema is a PostgreSQL extension that adds the ability to validate PostgreSQL's built-in json and jsonb data types against JSON Schema documents.

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:

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

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.

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