README.md

Is jsonb valid

is_jsonb_valid is a native PostgreSQL extension to validate json schemas following Draft4. and Draft7 The extension exposes two function is_jsonb_valid(schema jsonb, data jsonb) and is_jsonb_valid_draft_v7(schema jsonb, data jsonb) which return a boolean depending on the success of the validation. is_jsonb_valid validates following draft 4 of json schema.

Examples:

SELECT is_jsonb_valid('{"type": "number"}', '1');
> t
SELECT is_jsonb_valid('{"type": "object"}', '1');
> f

SELECT is_jsonb_valid_draft_v7('{"if":{"exclusiveMaximum":0},"else":{"multipleOf":2}}', '4');
> t
SELECT is_jsonb_valid_draft_v7('{"if":{"exclusiveMaximum":0},"else":{"multipleOf":2}}', '3');
> f

It passes (most of) JSON-Schema-Test-Suite. The exceptions are:

• $refRemote has been removed for obvious reasons.
• $ref support is limited to references nested at root, that is, something like `"$ref": "#/definitions/myschema". In particular, it doesn't check for "$id"` in the chain to the root, and it doesn't support remote refs.
• format is not supported (this is optional in the draft).

Testing and Installation

Make sure that you have PostgreSQL 9.6 or newer (check ci.yml for supported versions). In the directory of the project do:

make install && make installcheck

This will compile the extension and run the tests. Later in psql run:

CREATE EXTENSION is_jsonb_valid;

You can also run tests without installing postgres.

 docker run -it --rm --mount "type=bind,src=$(pwd),dst=/repo" pgxn/pgxn-tools     sh -c 'cd /repo && make clean && pg-start 12 && pg-build-test'  >log 2>&1

Running tests interactively

First terminal:

docker stop  pgx-test-v1 && docker rm  pgx-test-v1
docker run -it --name pgx-test-v1 --mount "type=bind,src=$(pwd),dst=/repo" pgxn/pgxn-tools bash
pg-start 12
tail -f /var/log/postgresql/postgresql-12-test.log

Second terminal:

docker exec -it pgx-test-v1 bash
cd repo/ && make clean  && make all PROFILE="-Werror" && sudo make install
make installcheck PGUSER=postgres

Troubleshooting

If the latest version of postgres is not found, just run

docker pull pgxn/pgxn-tools

Benchmarking

Benchmarking is always tricky, I've tried to check against a real world example, in particular tweets. The only other extension that I know for this purpose is postgres-json-schema. For more information on how to run the benchmarks check ./tools/README.md

Numbers of tweets	is_jsonb_valid (ms)	postgres-json-schema (ms)	Improvement (times)
10	34.270	192.678	5.6
100	206.378	1975.543	9.6
10000	8911.354	203172.464	22.8

Disclaimer

This project is a based on postgres-json-schema. It has been written from scratch in C (original was written in SQL).