Checking a JSON Column Against a Schema in PostgreSQL

PostgreSQL stores JSON in a jsonb column without asking what is inside. That freedom is the point, and it is also how a table ends up with {"id": "1"} in one row and {"id": 1} in the next, or a row whose document is an array. There is no CHECK (doc VALIDATES schema) in core PostgreSQL, but CHECK constraints built from the JSON operators cover the checks that matter most: the document is an object, the required keys exist, the types are right and values come from a list. I built one on a table of 200,000 events and recorded what it blocks, what it misses, and what it costs.

A constraint from plain operators

ALTER TABLE evc ADD CONSTRAINT evc_shape CHECK (
  jsonb_typeof(doc) = 'object'
  AND doc ?& array['id','ts','user','event']
  AND jsonb_typeof(doc->'id') = 'number'
  AND doc->>'event' IN ('view','click','buy','refund')
  AND doc#>>'{user,plan}' IN ('free','pro','team')
  AND coalesce(jsonb_typeof(doc#>'{props,ms}'), 'number') = 'number'
) NOT VALID;

Each line maps to a schema keyword. jsonb_typeof(doc) = 'object' is type: object. doc ?& array[...] is true when all the keys exist at the top level, which is required. jsonb_typeof(doc->'id') = 'number' is type for one field. The IN lists are enum. The last line handles an optional nested field: when props.ms is absent, jsonb_typeof returns NULL, and coalesce turns that into an accepted 'number'. Without the coalesce, an absent optional field would make the whole expression NULL, and I will come back to what that does.

With the constraint in place, bad inserts fail with the usual message:

insert into evc values ('{"id": 1, "event": "view"}');
ERROR:  new row for relation "evc" violates check constraint "evc_shape"
DETAIL:  Failing row contains ({"id": 1, "event": "view"}).

A document with a string id ({"id":"1","ts":"x",...}) and an array ([1,2]) were rejected the same way. The error names the constraint, not the rule inside it, so give the constraint a name that says what it checks and consider splitting it into several, one per rule (evc_has_keys, evc_event_enum), so the failure message tells the caller which one broke.

NOT VALID and VALIDATE

On a table that already has data, adding a constraint normally scans every row and takes a lock while it does. NOT VALID skips the scan: the constraint applies to new and updated rows immediately and the old rows are not checked. Then:

ALTER TABLE evc VALIDATE CONSTRAINT evc_shape;

checks the existing rows with a lighter lock (it does not block writes). On my table it failed:

ERROR:  check constraint "evc_shape" of relation "evc" is violated by some row

The message does not say which row. Find them with the opposite of the condition:

SELECT doc->>'id' AS id, doc#>'{props,ms}' AS ms
FROM evc
WHERE NOT (coalesce(jsonb_typeof(doc#>'{props,ms}'), 'number') = 'number')
ORDER BY 1::int LIMIT 3;

  id  |   ms
------+--------
 1000 | "slow"
 2000 | "slow"
 3000 | "slow"

There were 200 of them, the same records the Python and Node validators flagged in the JSON Schema guide. Fix or delete them, then validate again. The two-step approach is the safe way to introduce a constraint on a busy table: the rule is in force against new bad data from the first statement, and the cleanup of old data happens on your schedule. A table with no existing problems takes ADD CONSTRAINT directly. In my test, adding the constraint to a clean table of 199,800 rows took 0.13 s.

What the constraint costs on insert

I inserted the 199,800 valid documents into two identical tables, one without the constraint and one with it, three times each:

without constraint   0.37 s   0.29 s   0.26 s
with constraint      0.49 s   0.44 s   0.44 s

The check adds roughly 0.15 s per 200,000 rows, about 0.8 microseconds per row, on a table that took 0.3 s to fill. That is a visible percentage and an invisible amount of time: in an application inserting a few rows per request, it vanishes. If the constraint calls several functions on large documents, measure it on your documents. The check runs on every insert and on every update that rewrites the row, including updates to unrelated columns, so a very expensive constraint on a hot table deserves a look.

The NULL trap with jsonpath

PostgreSQL's jsonpath predicate operator @@ makes constraints shorter:

ALTER TABLE c1 ADD CONSTRAINT chk CHECK (doc @@ '$.id > 0');

The table rejected {"id": -5} as expected. It also accepted {"id": "1"}. The reason is how SQL treats CHECK: a constraint fails only when its expression is false, and passes when it is true or NULL. Comparing a string to a number in a jsonpath predicate is neither true nor false, it is unknown, and @@ returns NULL:

select doc, doc @@ '$.id > 0' as chk_value from c1;
     doc     | chk_value
-------------+-----------
 {"id": "1"} |

The same happens in strict mode when a key is missing (strict $.id > 0 against a document without it returned NULL). The fix is to make NULL fail on purpose:

CHECK (coalesce(doc @@ '$.id > 0', false))

With that, {"id": "1"} was rejected and {"id": 1} accepted. An alternative that is explicit about types is to add $.id.type() == "number" to the predicate; @@ against '{"id":"1"}' @@ '$.id.type() == "number"' returned false, which a CHECK rejects. Either way, test every constraint with a document of the wrong type, not just one with a wrong value. A jsonpath CHECK on the whole table, doc @@ '$.id > 0 && $.user.plan like_regex "^(free|pro|team)$" && $.event like_regex "^(view|click|buy|refund)$"', validated 199,800 rows in 0.26 s. The jsonpath syntax is covered in PostgreSQL SQL/JSON path expressions.

Related behaviors worth knowing, from the same session:

• jsonb_path_match(doc, path) requires the path to produce exactly one boolean. A path that returned two values raised single boolean result is expected, and a string compared with a number returned NULL, with or without silent => true, so it carries the same trap as @@.

• Lax mode, the default, converts structural errors to empty results. '[1]'::jsonb @@ '$.id > 0' returned false (not an error) for an array document. Strict mode raises errors for missing keys instead. Pick the mode that matches what you want a bad document to do.

• IS JSON is for text: '[1,' IS JSON is false and '{"a":1,"a":2}' IS JSON WITH UNIQUE KEYS is false, which is how to check a text column or an incoming string before casting. A jsonb value has already been parsed, and jsonb collapses duplicate keys on input.

Reusable shapes: domains

If several tables hold the same kind of document, define the check once as a domain:

CREATE DOMAIN plan_doc AS jsonb
  CHECK (VALUE ? 'plan' AND jsonb_typeof(VALUE->'plan') = 'string');

CREATE TABLE subscriptions (id bigint PRIMARY KEY, settings plan_doc NOT NULL);

'{"plan":1}'::plan_doc failed with value for domain plan_doc violates check constraint "plan_doc_check", and '{"plan":"pro"}'::plan_doc passed. A domain also applies to function arguments and casts, which a table constraint does not, and altering the rule later is one ALTER DOMAIN instead of one change per table.

A full JSON Schema in the database

For a real JSON Schema check, PostgreSQL needs an extension or a function. Extensions exist that expose a function such as jsonb_matches_schema(schema, doc), and PL/pgSQL implementations of JSON Schema exist as well. I did not run any of them: SELECT name FROM pg_available_extensions WHERE name ILIKE '%json%' returned no rows on the standard image, so core PostgreSQL has nothing of the kind. If you add one, benchmark it on your documents, because a general schema interpreter in SQL is much slower than a handwritten constraint, and check whether your managed provider allows the extension at all.

Most teams settle on a split. The database enforces the cheap, stable rules (it is an object, the keys exist, the types and the enums are right), and the application validates the full schema before the insert, with the schema file under version control (validating JSON in Python with a schema). The constraint is the safety net for writers that bypass the application: migrations, scripts and the one person with a psql session.

Keep the queryable parts as columns

A constraint on a JSON column is a sign that parts of the document are really structured. If you check id, event and user.plan on every row, and filter by them in queries, consider promoting them to real columns with real types and foreign keys, and keeping the rest as jsonb (storing settings as JSON in SQLite makes the same argument for another engine, and JSONB: when it is the wrong choice goes through the signs). A generated column can do it without changing the writers: id bigint GENERATED ALWAYS AS ((doc->>'id')::bigint) STORED. The cast then fails at insert time for a bad id, which is a type check by another name.

When a CHECK constraint is not enough

The rule needs another row or table. A CHECK sees one row. "The plan must exist in the plans table" is a foreign key on a promoted column, or a trigger.

The rule is long or changes often. A forty-line constraint is hard to read and each change is a migration. Keep the long rules in the application schema.

The documents are huge. The check runs on every write that touches the row, and detoasting a multi-megabyte document to check one key is costly. Measure before you add it to a hot table.

You need a full report of problems. A CHECK fails at the first false clause with one message. For per-field error lists, validate in the application.

The syntax of constraints is in the PostgreSQL manual under CHECK constraints, the JSON operators under JSON functions and operators, and NOT VALID in the ALTER TABLE reference. To look at a document that fails, paste it into the JSON formatter.