https://github.com/supabase/pg_jsonschema Skip to content Sign up * Product + Features + Mobile + Actions + Codespaces + Copilot + Packages + Security + Code review + Issues + Discussions + Integrations + GitHub Sponsors + Customer stories * Team * Enterprise * Explore + Explore GitHub + Learn and contribute + Topics + Collections + Trending + Skills + GitHub Sponsors + Open source guides + Connect with others + The ReadME Project + Events + Community forum + GitHub Education + GitHub Stars program * Marketplace * Pricing + Plans + Compare plans + Contact Sales + Education [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this organization All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} supabase / pg_jsonschema Public * * Notifications * Fork 0 * Star 182 PostgreSQL extension providing JSON Schema validation License Apache-2.0 license 182 stars 0 forks Star Notifications * Code * Issues 2 * Pull requests 0 * Actions * Projects 0 * Wiki * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Wiki * Security * Insights supabase/pg_jsonschema This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. master Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags 2 branches 0 tags Code Latest commit @olirice olirice Merge pull request #2 from supabase/benchmark ... fb7ab09 Jul 20, 2022 Merge pull request #2 from supabase/benchmark Add benchmark to readme fb7ab09 Git stats * 8 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time .cargo initial poc Mar 22, 2022 src expand tests Jul 20, 2022 .gitignore initial poc Mar 22, 2022 Cargo.toml pg_json_schema -> pg_jsonschema Jul 20, 2022 LICENSE initial poc Mar 22, 2022 README.md add benchmark Jul 20, 2022 pg_jsonschema.control pg_json_schema -> pg_jsonschema Jul 20, 2022 View code [ ] pg_jsonschema Summary API Usage Installation Prior Art Benchmark System Setup README.md pg_jsonschema PostgreSQL version License --------------------------------------------------------------------- Source Code: https://github.com/supabase/pg_jsonschema --------------------------------------------------------------------- Summary pg_jsonschema is a PostgreSQL extension adding support for JSON schema validation on json and jsonb data types. API SQL functions: -- Validates a json *instance* against a *schema* json_matches_schema(schema json, instance json) returns bool and -- Validates a jsonb *instance* against a *schema* jsonb_matches_schema(schema json, instance jsonb) returns bool Usage Those functions can be used to constrain json and jsonb columns to conform to a schema. For example: 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 ) ) ); -- 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]}). Installation Requires: * pgx cargo pgx run which drops into a psql prompt. psql (13.6) Type "help" for help. pg_jsonschema=# create extension pg_jsonschema; CREATE EXTENSION pg_jsonschema=# select json_matches_schema('{"type": "object"}', '{}'); json_matches_schema --------------------- t (1 row) for more complete installation guidelines see the pgx docs. Prior Art postgres-json-schema - an implementation of JSON Schema for Postgres written in PL/pgSQL Benchmark System * 2021 MacBook Pro M1 Max (32GB) * macOS 12.4 * PostgreSQL 14.1 Setup Validating the following schema on 20k unique inserts { "type": "object", "properties": { "a": {"type": "number"}, "b": {"type": "string"} } } create table bench_test_pg_jsonschema( meta jsonb, check ( jsonb_matches_schema( '{"type": "object", "properties": {"a": {"type": "number"}, "b": {"type": "string"}}}', meta ) ) ); insert into bench_test_pg_jsonschema(meta) select json_build_object( 'a', i, 'b', i::text ) from generate_series(1, 200000) t(i); -- Query Completed in 2.18 seconds for comparison, the equivalent test using postgres-json-schema's validate_json_schema function ran in 5.54 seconds. pg_jsonschema's ~2.5x speedup on this example JSON schema grows quickly as the schema becomes more complex. About PostgreSQL extension providing JSON Schema validation Resources Readme License Apache-2.0 license Code of conduct Code of conduct Stars 182 stars Watchers 20 watching Forks 0 forks Releases No releases published Sponsor this project Sponsor Learn more about GitHub Sponsors Packages 0 No packages published Languages * Rust 100.0% Footer (c) 2022 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.