Database

pg_jsonschema: JSON Schema Validation

JSON Schema is a language for annotating and validating JSON documents. pg_jsonschema is a Postgres extension that adds the ability to validate PostgreSQL's built-in json and jsonb data types against JSON Schema documents.

Enable the extension

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for pg_jsonschema and enable the extension.

Functions

Usage

Since pg_jsonschema exposes its utilities as functions, we can execute them with a select statement:

1
select
2
  extensions.json_matches_schema(
3
    schema := '{"type": "object"}',
4
    instance := '{}'
5
  );

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.

1
create table customer(
2
    id serial primary key,
3
    ...
4
    metadata json,
5
6
    check (
7
        json_matches_schema(
8
            '{
9
                "type": "object",
10
                "properties": {
11
                    "tags": {
12
                        "type": "array",
13
                        "items": {
14
                            "type": "string",
15
                            "maxLength": 16
16
                        }
17
                    }
18
                }
19
            }',
20
            metadata
21
        )
22
    )
23
);
24
25
-- Example: Valid Payload
26
insert into customer(metadata)
27
values ('{"tags": ["vip", "darkmode-ui"]}');
28
-- Result:
29
--   INSERT 0 1
30
31
-- Example: Invalid Payload
32
insert into customer(metadata)
33
values ('{"tags": [1, 3]}');
34
-- Result:
35
--   ERROR:  new row for relation "customer" violates check constraint "customer_metadata_check"
36
--   DETAIL:  Failing row contains (2, {"tags": [1, 3]}).

Resources