JSON document validation

It is a lot harder to fix bad data stored in your database than it is to keep the bad data out in the first place. Traditional relational databases had some ways to ensure that you were only putting integers into integer columns, and there were ways to make sure that the data was in a specified range. But, with more reliance on MySQL native JSON data type, there was no way until recently to make sure that certain key/values were present, of the right data type, and in a proper range. Thanks to the folks at JSON-Schema.org, MySQL 8.0 can help keep your data clean by rejecting bad JSON data.

CREATE TABLE testx (

col JSON,

CONSTRAINT myage_inRange

CHECK (JSON_SCHEMA_VALID('{"TYPE": "object",

"properties": {

"myage": {

"TYPE": "NUMBER",

"minimum": 28,

"maximum": 99

}

},"required": ["myage"]

}', col) = 1)

);

An example of how bad data is kept out of your database:

mysql> INSERT INTO testx VALUES('{"myage":27}');

ERROR 3819 (HY000): CHECK CONSTRAINT 'myage_inRange' IS violated.

mysql> INSERT INTO testx VALUES('{"myage":97}');

Query OK, 1 ROW affected (0.02 sec)

And yes, constraint checks now work with MySQL 8.0, where in the past they were ignored.