Contents

• JSON Schema Postgres Extension
  • Installation
  • Dependencies
  • Prior Art
  • Benchmark
  • Copyright and License

JSON Schema Postgres Extension

Change Log | Documentation

This package provides the jsonschema extension for validating JSON and JSONB against a JSON Schema in Postgres. It relies on the boon crate, and therefore supports the following specification drafts as validated by the JSON-Schema-Test-Suite excluding optional features:

• 
• 
• 
• 
• 

Installation

The jsonschema extension is written in Rust, using the boon JSON Schema validation library, and requires the Rust toolchain and pgrx to build. The simplest way to install Rust is rustup:

curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

Then install pgrx:

make install-pgrx

Now build and jsonschema against a working PostgreSQL server, including development libraries and pg_config, which must be in the path:

make
make install

To build with a different pg_config, pass it to make:

make PG_CONFIG=/path/to_pg_config
make install PG_CONFIG=/path/to_pg_config

Once jsonschema is installed, you can add it to a database. Simply connect to a database as a super user and run the CREATE EXTENSION command:

CREATE EXTENSION jsonschema;

If you want to install jsonschema into a specific schema, WITH SCHEMA:

CREATE EXTENSION jsonschema WITH SCHEMA extensions;

See the documentation for usage details and features.

Dependencies

The jsonschema data type has no runtime dependencies other than PostgreSQL. At build time it requires Rust and pgrx.

Prior Art

• pg_jsonschema: JSON Schema Postgres extension written with pgrx + the jsonschema crate
• pgx_json_schema: Slightly older JSON Schema Postgres extension written with pgrx + the jsonschema crate
• postgres-json-schema: JSON Schema Postgres extension written in PL/pgSQL
• is_jsonb_valid: JSON Schema Postgres extension written in C

Benchmark

A quick benchmark in eg/bench.sql compares the performance for a simple validation a check constraint between the jsonschema and pg_jsonschema. Example testing jsonschema with PostgreSQL 16 on an M3 Max MacBook Pro with 32G of RAM:

$ psql -f eg/bench.sql -X --set extension=jsonschema

######################################################################
# Test jsonschema JSON validation for 200_000 iterations
######################################################################
Time: 2686.546 ms (00:02.687)

######################################################################
# Test jsonschema JSONB validation for 200_000 iterations
######################################################################
Time: 2643.178 ms (00:02.643)

Testing pg_jsonschema:

$ psql -f eg/bench.sql -X --set extension=pg_jsonschema

######################################################################
# Test pg_jsonschema JSON validation for 200_000 iterations
######################################################################
Time: 1855.604 ms (00:01.856)

######################################################################
# Test pg_jsonschema JSONB validation for 200_000 iterations
######################################################################
Time: 1834.598 ms (00:01.835)

And a control test with no validation:

$ psql -f eg/bench.sql -X

######################################################################
# Test without JSON validation for 200_000 iterations
######################################################################
Time: 668.716 ms

######################################################################
# Test without JSONB validation for 200_000 iterations
######################################################################
Time: 741.202 ms

Copyright and License

Copyright (c) 2024 Tembo

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.