pg_jsonschema: JSON Schema support for Postgres

Summary

pg_jsonschema is a Postgres extension released on the Supabase platform that adds JSON Schema validation for json and jsonb data types. The post addresses a trade-off in storing complex webhook payloads as documents: JSON simplifies the model and accommodates changing payloads, but an unconstrained column cannot enforce required structure or types. It introduces JSON Schema as a formal, human-readable way to describe those constraints, including an object with a required string foo attribute and no additional properties. The extension provides json_matches_schema and jsonb_matches_schema, which can be used in a Postgres CHECK constraint to validate inserted documents. In the example, the constraint handled all eight test cases correctly, and invalid rows produced a constraint-violation error.

Takeaways

1. 1

   The demonstrated JSON Schema requires an object, a string-valued foo property, and no additional properties.

2. 2

   json_matches_schema accepts a JSON Schema and json instance, while jsonb_matches_schema accepts a JSON Schema and jsonb instance.

3. 3

   The extension can be enabled with create extension pg_jsonschema; in the example, failed inserts raise a PostgreSQL check-constraint violation with SQL state 23514.