JSON Schema Validation in MySQL and MariaDB

MySQL and MariaDB both provide a JSON_SCHEMA_VALID(schema, document) function, but the same function name does not guarantee that the same schema behaves the same way. MySQL 8.0.17 and later implements JSON Schema Draft 4; MariaDB 11.1 and later implements Draft 2020 with documented exceptions.

Quick comparison

For a quick browser-side Draft 2020-12 check, the MariaDB JSON_SCHEMA_VALID() guide includes a local checker. It does not execute SQL or reproduce either server’s exact behavior; use the target database for authoritative results.

MySQL does not support external schema resources or the $ref keyword, and it silently ignores invalid regular-expression patterns. MariaDB documents that external schema resources are unsupported, format values are annotations, and Hyper-schema keywords are unsupported. Check the target engine’s dialect and limitations before moving a schema between servers. See MySQL’s JSON Schema validation reference and MariaDB’s JSON_SCHEMA_VALID() reference.

Validate with the shared keyword subset

Both engines accept the common type, properties, required, and minimum keywords in this example:

SET @schema = '{
  "type": "object",
  "properties": {"id": {"type": "integer", "minimum": 1}},
  "required": ["id"]
}';

SELECT
    JSON_SCHEMA_VALID(@schema, '{"id": 7}') AS valid_document,
    JSON_SCHEMA_VALID(@schema, '{"id": 0}') AS invalid_document;

+----------------+------------------+
| valid_document | invalid_document |
+----------------+------------------+
| 1              | 0                |
+----------------+------------------+

This uses a small set of keywords common to both implementations; it does not make arbitrary Draft 4 and Draft 2020 schemas portable. Both functions accept the schema first and the document second. A SQL NULL argument returns NULL in MySQL; check MariaDB’s behavior on the target release rather than assuming every edge case matches.

Get failure details in MySQL

MySQL provides JSON_SCHEMA_VALIDATION_REPORT() in addition to the Boolean check. Its JSON report can include the failed keyword and JSON Pointer locations for the schema and document:

SELECT JSON_SCHEMA_VALIDATION_REPORT(
    '{"type":"object","properties":{"id":{"type":"integer","minimum":1}},"required":["id"]}',
    '{"id":0}'
) AS validation_report;

Use this function when an application needs to show why a value failed. MariaDB’s documented JSON_SCHEMA_VALID() interface returns only 1 or 0. See the MySQL JSON_SCHEMA_VALIDATION_REPORT() guide.

Enforce the schema when writing rows

Both engines can use JSON_SCHEMA_VALID() in a CHECK constraint. MySQL added JSON Schema validation in 8.0.17, after CHECK constraints became enforced in 8.0.16. MariaDB’s JSON alias also checks JSON syntax, but a schema constraint is still needed to require properties or restrict values. See the engine-specific guides for MySQL and MariaDB.

For MySQL’s JSON storage format and MariaDB’s LONGTEXT alias, see the JSON data type comparison. For other cross-engine SQL tasks, browse the SQL comparison guides.