pg_jsonschema: JSON Schema Validation
JSON Schema is a language for annotating and validating JSON documents. pg_jsonschema is a PostgreSQL extension that adds the ability to validate PostgreSQL's built-in json and jsonb data types against JSON Schema documents.
Enable the extension
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_jsonschema" and enable the extension.
Functions
json_matches_schema(schema json, instance json): Checks if ajsoninstance conforms to a JSON Schema schema.jsonb_matches_schema(schema json, instance jsonb): Checks if ajsonbinstance conforms to a JSON Schema schema.
Usage
Since pg_jsonschema exposes its utilities as functions, we can execute them with a select statement:
_10select_10 extensions.json_matches_schema(_10 schema := '{"type": "object"}',_10 instance := '{}'_10 );
pg_jsonschema is generally used in tandem with a check constraint as a way to constrain the contents of a json/b column to match a JSON Schema.
_36create table customer(_36 id serial primary key,_36 ..._36 metadata json,_36_36 check (_36 json_matches_schema(_36 '{_36 "type": "object",_36 "properties": {_36 "tags": {_36 "type": "array",_36 "items": {_36 "type": "string",_36 "maxLength": 16_36 }_36 }_36 }_36 }',_36 metadata_36 )_36 )_36);_36_36-- Example: Valid Payload_36insert into customer(metadata)_36values ('{"tags": ["vip", "darkmode-ui"]}');_36-- Result:_36-- INSERT 0 1_36_36-- Example: Invalid Payload_36insert into customer(metadata)_36values ('{"tags": [1, 3]}');_36-- Result:_36-- ERROR: new row for relation "customer" violates check constraint "customer_metadata_check"_36-- DETAIL: Failing row contains (2, {"tags": [1, 3]}).
Resources
- Official
pg_jsonschemadocumentation