Using MySQL 8.0 JSON Schema Validation Functions

Introduction

Starting with MySQL 8.0.17, the server can strictly validate JSON data against a predefined JSON schema. A JSON schema defines the expected structure, data types, default values, character sets, and other constraints, similar to a relational table definition. For deeper understanding, refer to https://json-schema.org/understanding-json-schema/.

Schema Example

The following schema describes an array where the first element must be a number ≥ 5, the second a string of length ≥ 5, the array must contain between 2 and 5 items, and all items must be unique.

{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "title": "ytt array",
  "description": "ytt array",
  "type": "array",
  "items": [
    {"type": "number", "minimum": 5},
    {"type": "string", "minLength": 5}
  ],
  "minItems": 2,
  "maxItems": 5,
  "uniqueItems": true
}

MySQL provides two built‑in functions for schema validation:

json_schema_valid – returns 1 if the JSON document conforms to the schema, otherwise 0.

json_schema_validation_report – returns a detailed JSON report describing why validation succeeded or failed.

Array Validation Demo

First, pretty‑print the schema with json_pretty after adding an enum element for illustration.

mysql:ytt>select json_pretty(@schema)\G
*************************** 1. row ***************************
json_pretty(@schema): {
  "type": "array",
  "items": [
    {"type": "number", "minimum": 5},
    {"type": "string", "minLength": 5},
    {"enum": ["postgresql", "oracle", "uguard", "tidb"]}
  ],
  "title": "ytt array",
  "$schema": "http://json-schema.org/draft-04/schema#",
  "maxItems": 5,
  "minItems": 3,
  "description": "ytt array",
  "uniqueItems": true
}
1 row in set (0.01 sec)

Test several JSON arrays against the schema:

mysql:ytt>set @a='[]';
mysql:ytt>set @b='["mysql",100,"tidb"]';
mysql:ytt>set @c='[100,"mysql","uguard"]';
mysql:ytt>set @d='[200,"database","postgresql","others"]';
mysql:ytt>select json_schema_valid(@schema,@a) "@a",
               json_schema_valid(@schema,@b) "@b",
               json_schema_valid(@schema,@c) "@c",
               json_schema_valid(@schema,@d) "@d";
+------+------+------+------+ 
| @a   | @b   | @c   | @d   |
+------+------+------+------+ 
| 0    | 0    | 1    | 1    |
+------+------+------+------+
1 row in set (0.00 sec)

Arrays @a and @b fail; @c and @d pass. Detailed failure reasons can be obtained with json_schema_validation_report :

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@a)) "@a result"\G
@a result: {
  "valid": false,
  "reason": "The json document location '#' failed requirement 'minItems' at json Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "minItems"
}

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@b)) "@b result"\G
@b result: {
  "valid": false,
  "reason": "The json document location '#/0' failed requirement 'type' at json Schema location '#/items/0'",
  "schema-location": "#/items/0",
  "document-location": "#/0",
  "schema-failed-keyword": "type"
}

Thus @a violates the minItems rule, while @b violates the type rule for the first element.

Object Validation Demo

The next schema defines an object with required keys x (number) and y (string), an optional z (enum), and limits the total number of properties to 2‑3.

mysql:ytt>select json_pretty(@schema)\G
json_pretty(@schema): {
  "type": "object",
  "title": "ytt object",
  "$schema": "http://json-schema.org/draft-04/schema#",
  "required": ["x", "y"],
  "properties": {
    "x": {"type": "number"},
    "y": {"type": "string"},
    "z": {"enum": ["mysql", "ugurad", "postgresql"]}
  },
  "description": "ytt object",
  "maxProperties": 3,
  "minProperties": 2
}

Validate two JSON objects against this schema:

mysql:ytt>set @a='{"x":"mysql","y":"oracle"}';
mysql:ytt>set @b='{"x":10,"y":"uguard","z":"mysql"}';
mysql:ytt>select json_schema_valid(@schema,@a) "@a",
               json_schema_valid(@schema,@b) "@b";
+------+------+ 
| @a   | @b   |
+------+------+ 
| 0    | 1    |
+------+------+ 
1 row in set (0.00 sec)

Object @a fails because the value of x is a string instead of a number. The detailed report confirms this:

mysql:ytt>select json_pretty(json_schema_validation_report(@schema,@a)) "@a result"\G
@a result: {
  "valid": false,
  "reason": "The json document location '#/x' failed requirement 'type' at json Schema location '#/properties/x'",
  "schema-location": "#/properties/x",
  "document-location": "#/x",
  "schema-failed-keyword": "type"
}

Applying Schema Checks to Tables

The same object schema can be used in a CHECK constraint on a JSON column:

create table t10 (
  str1 json,
  check (json_schema_valid(
    '{
      "$schema": "http://json-schema.org/draft-04/schema#",
      "title":"ytt object",
      "description": "ytt object",
      "type": "object",
      "properties": {
        "x": { "type": "number" },
        "y": { "type": "string" },
        "z": { "enum": ["mysql", "ugurad", "postgresql"] }
      },
      "required": ["x", "y"],
      "minProperties": 2,
      "maxProperties": 3
    }', str1))
);

Inserting @a fails with an error indicating the type mismatch for x , while inserting @b succeeds.

Conclusion

The json_schema_valid and json_schema_validation_report functions allow developers to pre‑validate JSON data against a strict schema before persisting it, reducing the risk of malformed data in tables that use the JSON data type.