Open
Description
pg_jsonschema is a Postgres extension that provides built-in function for JSON schema validation. This allows us to validate a JSON schema against a SQL schema object. We should support the json_matches_schema built-in in CockroachDB that can accomplish this functionality. This is also something that is supported by Oracle DB.
The result of this built-in can be used as part of the check constraint when declaring the schema for a table:
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
}
}
}
}'::json,
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]}).There are three built-ins that pg_jsonschema supports and we can do this in phases:
- jsonb_matches_schema(schema json, instance jsonb) returns bool
- json_matches_schema(schema json, instance json) returns bool
- jsonschema_is_valid(schema json) returns bool
Jira issue: CRDB-31110