When we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.

If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.

Example

Here’s an example to demonstrate:

CREATE TABLE stats (
    scores JSON,
    CHECK(
        JSON_SCHEMA_VALID(
            '{
                "type": "object",
                "properties": {
                "playerId": {
                "description": "The unique identifier for a player",
                "type": "integer"
                },
                "playerName": {
                "description": "Name of the player",
                "type": "string"
                },
                    "score": {
                    "description": "The price of the product",
                    "type": "integer",
                     "minimum": 0,
                     "maximum": 500
                    }
                },
                "required": [ "playerId", "playerName", "score" ]
            }',
            scores
        )
    )
);

That code created a table called stats with a JSON column called scores.

We created a CHECK constraint that uses the JSON_SCHEMA_VALID() function to check any JSON document that’s inserted into the column. The JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema. It returns true (1) if the document is valid, and false (0) if it is not. Therefore if we try to insert a JSON document that doesn’t adhere to the schema, the function will return 0, and the insert will fail.

The above CHECK constraint checks that the JSON document contains all three fields, that their data type is correct, and that the score field contains a value between 0 and 500.

Now let’s insert data into the table:

INSERT INTO stats ( scores ) 
    VALUES (
        '{
            "playerId" : 1,
            "playerName" : "Bart",
            "score" : 427
        }'
    );

Now let’s take a look at the contents of our table:

SELECT * FROM stats;

Result:

+-----------------------------------------------------+
| scores                                              |
+-----------------------------------------------------+
| {"score": 427, "playerId": 1, "playerName": "Bart"} |
+-----------------------------------------------------+

In this case the JSON was valid against the schema, and it was therefore inserted into the table.

Let’s try to insert a JSON document that violates the schema:

INSERT INTO stats ( scores ) 
    VALUES (
        '{
            "playerId" : 2,
            "playerName" : "Homer",
            "score" : -500
        }'
    );

Result:

ERROR 3819 (HY000): Check constraint 'stats_chk_1' is violated.

In this case our JSON document violated the schema. Specifically, this player has a negative score, but negative scores aren’t allowed.

Note that MySQL CHECK constraints can’t contain variables. That’s why we had to include the whole JSON schema within the CHECK constraint instead of passing it in a variable.