pg_jsonschema - Integrate JSON Schema Validation Efficiently into PostgreSQL

Introducing pg_jsonschema: Enhancing PostgreSQL with JSON Schema Validation

Overview

pg_jsonschema is a powerful PostgreSQL extension designed to enhance the database's capabilities by adding JSON schema validation. This tool streamlines the validation process for json and jsonb data types directly within the database environment.

API Functions

To achieve JSON schema validation, pg_jsonschema offers three primary functions:

json_matches_schema: Validates a json instance against a given schema.
```
json_matches_schema(schema json, instance json) returns bool
```
jsonb_matches_schema: Similar to the first, but specifically for jsonb instances.
```
jsonb_matches_schema(schema json, instance jsonb) returns bool
```
jsonschema_is_valid: Checks if a JSON schema itself is valid.
```
jsonschema_is_valid(schema json) returns bool
```

How It Works

These functions can be employed to enforce constraints on json and jsonb columns, ensuring that they adhere to the predefined schema.

Here's an example:

Create a table customer with a metadata column of json type.
Implement a check constraint to ensure that the metadata matches a specified schema.

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
        )
    )
);

This setup ensures that any insertion into the customer table meets the defined JSON structure, such as an array of strings for the "tags" property, each with a maximum length of 16 characters.

JSON Schema Support

pg_jsonschema acts as a minimal layer over the jsonschema Rust crate. It supports various JSON Schema drafts by leveraging the extensive capabilities of the Rust library. For complete information on supported drafts, users are encouraged to visit the jsonschema documentation.

Getting Started

To experiment with pg_jsonschema, it can be installed and run in a Docker environment using docker-compose. The extension can be tested by connecting to the PostgreSQL server via postgresql://postgres:password@localhost:5407/app.

Installation

Before installing pg_jsonschema, ensure that pgrx is installed. pgrx is essential for building and running PostgreSQL extensions written in Rust.

Run the following command to install:

cargo pgrx run

This command opens a psql prompt, allowing you to create the extension in your PostgreSQL instance:

pg_jsonschema=# create extension pg_jsonschema;

For detailed installation steps, refer to the pgrx documentation.

Previous Work

pg_jsonschema builds upon prior projects such as:

postgres-json-schema: A JSON Schema Postgres extension implemented in PL/pgSQL.
is_jsonb_valid: A similar extension written in C for JSON Schema validation.
pgx_json_schema: Developed using pgrx and the jsonschema crate.

Performance Benchmark

The performance of pg_jsonschema is noteworthy. In a benchmarking test involving 20,000 unique inserts against a defined schema, pg_jsonschema completed the task in 351 ms. This test was conducted on a 2021 MacBook Pro M1 Max with PostgreSQL 16.2. In comparison, another method utilizing postgres-json-schema took 5.54 seconds for the same task, showcasing pg_jsonschema's efficiency, especially as schemas grow in complexity.

In summary, pg_jsonschema is a robust tool for enhancing data accuracy and consistency within PostgreSQL databases by seamlessly integrating JSON Schema validation. From installation to execution, it offers ease of use and impressive performance boosts that make it an invaluable asset for database administrators and developers alike.