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#