pg_jsonschema

If you are validating many rows against the same schema (e.g., in a large table scan or bulk insert), you should use the compiled schema type.

By casting your schema to the jsonschema type once, the validator is compiled and cached per callsite. This avoids the overhead of recompiling the schema for every single row, providing significant performance gains (e.g., ~1.8x speedup in benchmarks).

Compiled Functions

• json_matches_compiled_schema(schema jsonschema, instance json)
• jsonb_matches_compiled_schema(schema jsonschema, instance jsonb)
• jsonschema_validation_errors_compiled(schema jsonschema, instance json)
• jsonb_validation_errors_compiled(schema jsonschema, instance jsonb)

-- Cast schema to jsonschema once to benefit from caching
SELECT jsonb_matches_compiled_schema(
    '{"type": "object"}'::jsonschema, 
    '{"key": "value"}'::jsonb
);

To install pg_jsonschema for development, you need pgrx installed. Run the following command to drop into a psql prompt with the extension available:

cargo pgrx run

Once in the psql prompt, enable the extension by running:

CREATE EXTENSION pg_jsonschema;

The jsonschema type is a specialized type designed for efficient validation. When you use functions like jsonb_matches_compiled_schema, the extension attempts to retrieve or compile a validator for the provided schema.

Key behaviors:

• Caching: The validator is cached to avoid re-compilation on every call.
• Schema Changes: If the same callsite is used with a different schema (e.g., in a SELECT over a table where the schema column changes), the extension detects the change and refreshes the validator.
• Storage: Because jsonschema is a first-class type, you can store compiled schemas in table columns to avoid re-parsing them from raw JSON every time you run a query.

The JsonSchema type is a specialized PostgreSQL type used to represent a JSON schema in its canonical string form.

When a JSON schema is passed to functions using this type, it undergoes a process of canonicalization, compilation, and caching:

1. Canonicalization: The schema is converted into a canonical JSON string. This ensures that semantically identical schemas result in the exact same string representation, which maximizes cache efficiency.
2. Compilation: The schema is compiled into a jsonschema::Validator.
3. Caching: The compiled validator is stored in a two-level cache (a per-callsite slot and a bounded backend-local LRU) to ensure high performance for repeated schema validations.

This type is primarily used as an input type for functions within the pg_jsonschema extension to ensure that schema validation is both fast and consistent.

The docker-compose.yaml file defines a PostgreSQL database service named db used for running pg_jsonschema.

Key configuration details:

• Container Name: pg_jsonschema_db
• Port Mapping: The host port 5407 is mapped to the container port 5432. Use localhost:5407 to connect from your host machine.
• Default Credentials:
  • User: postgres
  • Password: password
  • Database: app
• Healthcheck: The service uses pg_isready to ensure the database is ready for connections.

services:
  db:
    container_name: pg_jsonschema_db
    ports:
      - 5407:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: app

Use jsonschema_is_valid(schema: Json) -> bool to verify if a provided JSON object is a valid JSON Schema according to the meta-schema. If the schema is invalid, it will emit a PostgreSQL notice containing the path to the error.

SELECT jsonschema_is_valid('{"type":"object"}'); -- returns true
SELECT jsonschema_is_valid('{"type":"invalid_type"}'); -- returns false and emits a notice

The extension provides functions to compile a JSON or JSONB object into a jsonschema type, which is optimized for repeated validation.

• jsonschema_from_json(schema: Json) -> JsonSchema
• jsonschema_from_jsonb(schema: JsonB) -> JsonSchema

Additionally, the extension defines implicit casts so you can use the ::jsonschema syntax:

SELECT '{"type":"string"}'::json::jsonschema;
SELECT '{"type":"string"}'::jsonb::jsonschema;

-- Using the explicit function
SELECT jsonschema_from_json('{"type":"string"}');

-- Using the cast (preferred)
SELECT '{"type":"string"}'::jsonb::jsonschema;

If you need to know why a JSON instance failed validation against a schema, use jsonschema_validation_errors. This function returns an array of error messages as strings.

• jsonschema_validation_errors(schema: Json, instance: Json) -> Vec<String>

SELECT jsonschema_validation_errors(
    '{"maxLength":4}', 
    '"toolong"'
);
-- Returns: {"'toolong' is longer than 4 characters"}

Use these functions for one-off validation of a JSON or JSONB instance against a JSON schema. These functions are ideal for simple checks where the schema is not reused frequently, as the schema is re-parsed for every call.

• json_matches_schema(schema: Json, instance: Json) -> bool
• jsonb_matches_schema(schema: Json, instance: JsonB) -> bool

-- Example using JSON
SELECT json_matches_schema('{"type":"string"}', '"hello"'); -- returns true

-- Example using JSONB
SELECT jsonb_matches_schema('{"type":"string"}', '"hello"'::jsonb); -- returns true