Menu
I can't find any information about JSON schema validation in PostgreSQL, is there any way to implement JSON Schema validation on PostgreSQL JSON data type?
582
JSON Schema is a powerful tool. It enables you to validate your JSON structure and make sure it meets the required API. You can create a schema as complex and nested as you need, all you need are the requirements. You can add it to your code as an additional test or in run-time.
JSON Schema Validation: The JSON Schema Validation specification is the document that defines the valid ways to define validation constraints. This document also defines a set of keywords that can be used to specify validations for a JSON API. In the examples that follow, we'll be using some of these keywords.
JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance. Support for indexing.
There is another PostgreSQL extension that implements json validation. The usage is almost the same as "Postgres-JSON-schema"
CREATE TABLE example (id serial PRIMARY KEY, data jsonb); -- do is_jsonb_valid instead of validate_json_schema ALTER TABLE example ADD CONSTRAINT data_is_valid CHECK (is_jsonb_valid('{"type": "object"}', data)); INSERT INTO example (data) VALUES ('{}'); -- INSERT 0 1 INSERT INTO example (data) VALUES ('1'); -- ERROR: new row for relation "example" violates check constraint "data_is_valid" -- DETAIL: Failing row contains (2, 1). I've done some benchmarking validating tweets and it is 20x faster than "Postgres-JSON-schema", mostly because it is written in C instead of SQL.
Disclaimer, I've written this extension.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
