Agent Wranglers

DAT-77Recommended

Check the shape of JSON columns in the database

Give every JSON column a check constraint for the keys and types the code relies on. Move any value you filter, join or sum on into its own typed column.

By Agent Wranglers. Updated .

How to do it

A JSON column accepts any document, so a typo in one release writes rows that the next release cannot read. MySQL can enforce a JSON Schema with JSON_SCHEMA_VALID() inside a CHECK constraint. In PostgreSQL, combine the ?& operator and jsonb_typeof() in a CHECK. A constraint in the database still holds when a script or another service writes, as Enforce rules with constraints says. Pick jsonb as Choose exact types says.

Examples

SQL example

-- PostgreSQL
ALTER TABLE orders ADD CONSTRAINT shipping_shape CHECK (
  shipping ?& array['carrier', 'weight_grams']
  AND jsonb_typeof(shipping -> 'weight_grams') = 'number'
);

-- MySQL
ALTER TABLE orders ADD CONSTRAINT shipping_shape CHECK (JSON_SCHEMA_VALID(
  '{"type": "object", "required": ["carrier", "weight_grams"], "properties": {"weight_grams": {"type": "integer"}}}',
  shipping
));

How to check

Every JSON column in the schema has a CHECK constraint on it. On staging, an insert missing a required key is refused, and so is one with text where a number belongs.

Sources

For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/data/schema-design/json-column-checks">Agent Wranglers data and databases playbook</a>. In code you write, add a comment next to it with the same link.