Validating JSON Before It Gets Into a MySQL Column

A MySQL JSON column does exactly one check on its own: the text must be valid JSON. That is a low bar. An array, a bare string or an empty object goes in without complaint, as does a document with a misspelled key or a quantity written as "2". The mistakes surface later as NULLs in reports, as casts that fail, or as an application error in a job that nobody is watching. This article shows how to push the rules into the database with CHECK constraints, what JSON_SCHEMA_VALID supports and what it quietly does not, and what it costs.

What the type already checks

Syntax. The message names the problem and the position, as shown in fixing invalid JSON text errors in MySQL inserts, and the statement fails. Beyond that, nothing: the type has no idea what shape a document should have. So the rules about shape have to live in constraints, in application code, or in both. The wider picture of the type is in using the JSON column type in MySQL, and updates that must keep a document valid are in updating parts of a MySQL JSON document in place.

Simple rules with CHECK

Three functions cover the basic structural rules: JSON_TYPE for the kind of value, JSON_CONTAINS_PATH for required keys, and extraction plus JSON_TYPE for the kind of a field.

create table simple (
  id  int auto_increment primary key,
  doc json not null,
  check (json_type(doc) = 'OBJECT'),
  check (json_contains_path(doc, 'all', '$.sku', '$.qty')),
  check (json_type(json_extract(doc, '$.qty')) = 'INTEGER')
);

The first insert, {"sku":"a","qty":1}, was accepted. The rest failed:

'[1,2]'                       Check constraint 'simple_chk_1' is violated.
'{"sku":"a"}'                 Check constraint 'simple_chk_2' is violated.
'{"sku":"a","qty":"1"}'       Check constraint 'simple_chk_3' is violated.
'{"sku":"a","qty":1.5}'       Check constraint 'simple_chk_3' is violated.

Naming constraints helps: unnamed ones are simple_chk_1, simple_chk_2 and so on, which tells you nothing. Write constraint doc_is_object check (...) so the error reads like a rule.

A caution about JSON_TYPE and numbers: a JSON 2.0 is a DOUBLE, not an INTEGER, so a check for 'INTEGER' rejects it. That may be what you want, and it may break a client that sends 2.0 for a count because it serializes numbers as doubles. Check what your producers actually send before enforcing.

These checks are cheap and handle most of the structure that matters: it is an object, the required keys exist, the important fields have the right type. They do not look inside nested structures, do not validate patterns or ranges, and they get long.

Full structure with JSON_SCHEMA_VALID

JSON_SCHEMA_VALID(schema, document) validates a document against a JSON Schema and returns 1 or 0. It implements draft 4. Here is a schema for an order line, and what it did to twelve test documents:

{
  "type": "object",
  "required": ["sku", "qty"],
  "additionalProperties": false,
  "properties": {
    "sku":   {"type": "string", "pattern": "^SKU-[0-9]{6}$"},
    "qty":   {"type": "integer", "minimum": 1, "maximum": 100},
    "color": {"enum": ["red", "blue"]},
    "tags":  {"type": "array", "items": {"type": "string"}, "maxItems": 3, "uniqueItems": true},
    "price": {"type": "number", "exclusiveMinimum": 0}
  }
}

document                                         valid
{"sku":"SKU-000001","qty":2}                      1
{"sku":"SKU-1","qty":2}                           0   (pattern)
{"sku":"SKU-000001"}                              0   (missing qty)
{"sku":"SKU-000001","qty":2.5}                    0   (not an integer)
{"sku":"SKU-000001","qty":2.0}                    0   (a double is not an integer)
{"sku":"SKU-000001","qty":101}                    0   (maximum)
{"sku":"SKU-000001","qty":2,"color":"green"}      0   (enum)
{"sku":"SKU-000001","qty":2,"extra":1}            0   (additionalProperties)
{"sku":"SKU-000001","qty":2,"tags":["a","a"]}     0   (uniqueItems)
{"sku":"SKU-000001","qty":2,"price":0}            1   (!)
[1]                                               0   (not an object)
{"sku":"SKU-000001","qty":"2"}                    0   (string, not integer)

Eleven of twelve matched what the schema says. The one that did not is price: 0. In draft 4 exclusiveMinimum is a boolean modifier of minimum, and the numeric form "exclusiveMinimum": 0 is from draft 6 and later. MySQL ignored it, with no warning, and accepted a zero price. That is the main danger with a partial implementation: the validator reports success for rules it does not understand. Two more checks, from separate runs:

• "format": "email" is ignored: json_schema_valid('{"type":"string","format":"email"}', '"not an email"') returned 1. Use pattern for formats you care about.

• A schema with $ref to an external document is rejected: This version of MySQL doesn't yet support 'references in JSON Schema'. I did not test local references to definitions inside the same schema, so test them before relying on them.

An unknown type such as "bogus" returned 0 for every document. A second argument that is not valid JSON raises error 3141 and a NULL document returns NULL, which a CHECK treats as passing (NULL is not false), so keep NOT NULL on the column.

Putting it in a constraint

create table order_lines (
  id  int auto_increment primary key,
  doc json not null,
  constraint doc_ok check (json_schema_valid('{ ...the schema above, on one line... }', doc))
);

Inserting valid and invalid documents, the results were:

insert {"sku":"SKU-000001","qty":2}            ok
insert {"sku":"SKU-1","qty":2}                 ERROR 3819: Check constraint 'doc_ok' is violated.
insert {"sku":"SKU-000002","qty":2,"x":1}      ERROR 3819: Check constraint 'doc_ok' is violated.
update ... json_set(doc, '$.qty', 500)         ERROR 3819: Check constraint 'doc_ok' is violated.
update ... json_set(doc, '$.qty', 5)           ok
insert a multi-row statement with one bad doc  ERROR 3819, and neither row inserted

The constraint covers updates as well as inserts, which is where invalid data normally enters (a partial update with JSON_SET that pushes a field out of range). It is atomic per statement: the multi-row insert with a valid row and a bad one inserted neither, and the table still held one row.

The error message names the constraint, not the reason. To see the reason while developing, call the report function on the failing document:

select json_pretty(json_schema_validation_report(@schema, '{"sku":"SKU-1","qty":2,"extra":1}'));

It returned "valid": false with "reason": "The JSON document location '#/sku' failed requirement 'pattern' at JSON Schema location '#/properties/sku'" and the failing keyword. It reports the first failure, not all of them. INSERT IGNORE converts the error to a warning that includes the same text (Error 3934 The JSON document location '#/sku' failed requirement 'pattern'...), and skips the row.

The cost

Constraints run for each row. I inserted 50,000 documents of the order-line shape into three tables:

no check                           194 ms
type + required-key check          205 ms
full JSON_SCHEMA_VALID check       301 ms

The simple checks added about 6 percent, and the schema check about 55 percent. For ordinary traffic that is nothing. For a bulk load of hundreds of millions of rows it is a cost you can avoid by loading into a table without the constraint, validating in a query, and moving the valid rows. The same function is usable as a filter: select count(*) from t where not json_schema_valid(@schema, doc) finds the violators without touching the table definition.

Adding the rule to a table that has data

ALTER TABLE ... ADD CONSTRAINT ... CHECK validates every existing row and fails if one does not pass. I tried adding a deliberately impossible rule to a table of 50,000 rows and got Check constraint 'c1' is violated, with no list of offenders. Find them first:

select id from t where not json_schema_valid(@schema, doc) limit 20;

Clean or move them, then add the constraint. If you need the rule to apply to new data only, MySQL allows NOT ENFORCED: add constraint c2 check (...) not enforced is accepted, and information_schema.table_constraints shows ENFORCED: NO. A rule that is not enforced does nothing, so it documents intent and nothing else; switch it on once the table is clean with alter table ... alter check c2 enforced.

Where else to validate

The database is the last line of defense, and it should not be the only one. Validate in the application as the document is produced, with the same JSON Schema file if you can, and give the user a useful error with all the failures at once. A schema in the repository can be tested, reviewed and versioned; the one in a constraint is a string inside a DDL statement that nobody opens. See validating JSON with jsonschema in Python for the application side, and the JSON Schema generator to draft a schema from a sample document. Keep the two consistent: a schema in the application that allows exclusiveMinimum as a number while the database ignores it will pass in tests and fail nowhere.

When the database is the wrong place to validate

The schema changes often. Altering a check constraint takes a metadata lock and revalidates the table. A document format that evolves weekly needs validation in the application, with versioned schemas.

You need rich error messages. The database returns the name of a constraint. Users need to know which field is wrong. Validate before the insert.

The rules are beyond draft 4. If your schema uses if/then/else, exclusiveMinimum as a number, $ref to shared definitions or formats, MySQL either ignores the keyword or fails. Test each keyword on your version before relying on it.

Documents are huge. The validator parses the document on every write. For large documents the cost grows.

Different producers need different rules. One constraint per table. Use a version field and a schema per version in the application.

The functions are documented in the MySQL manual: JSON_SCHEMA_VALID and the report function, and CHECK constraints. The schema language is specified at json-schema.org.