MySQL JSON_SCHEMA_VALID() Function

In MySQL, the JSON_SCHEMA_VALID() function verifies a JSON document according to the specified JSON schema.

JSON_SCHEMA_VALID() is available in MySQL 8.0.17 and later. MySQL supports Draft 4 schemas, but not external schema resources or $ref; invalid regular-expression patterns are silently ignored. See MySQL’s JSON Schema validation documentation for these limits. Use JSON_SCHEMA_VALIDATION_REPORT() when you need the failed keyword and document location instead of only 1 or 0.

MariaDB has a function with the same name, but it supports Draft 2020 with some exceptions from MariaDB 11.1. Do not assume a schema will behave the same on both engines; see the MariaDB JSON_SCHEMA_VALID() guide when moving schemas between them.

For a side-by-side comparison of schema dialects, minimum versions, and failure reports, see JSON Schema validation in MySQL and MariaDB.

JSON_SCHEMA_VALID() Syntax

Here is the syntax of the MySQL JSON_SCHEMA_VALID() function:

JSON_SCHEMA_VALID(schema, json_doc)

Parameters

schema

Required. The JSON schema that must be a valid JSON object.

json_doc

Required. The JSON document to validate.

Return value

The JSON_SCHEMA_VALID() function returns 1 or 0; 1 indicates that the JSON document validates against the JSON schema, and 0 indicates that it does not.

• If either argument is NULL, the function returns NULL.
• If either argument is not valid JSON, MySQL raises ER_INVALID_TYPE_FOR_JSON.
• If the schema is valid JSON but is not an object, MySQL raises ER_INVALID_JSON_TYPE.

JSON_SCHEMA_VALID() Examples

This example shows how to use the JSON_SCHEMA_VALID() function to verify a JSON document according to a JSON schema.

First, let’s create a JSON schema:

SET @schema = '{
      "id": "http://json-schema.org/geo",
      "$schema": "http://json-schema.org/draft-04/schema#",
      "description": "A geographical coordinate",
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';

Here, we created a JSON schema. In it:

• "type": "object" indicates that the JSON document must be a JSON object.

• "properties" defines the list of members in the object, and the constraints for each member. Here’s the definition of two members:

  • x - Number type, the maximum value is 90, the minimum value is -90.
  • y - Number type, the maximum value is 180, the minimum value is -180.

• "required": ["x", "y"] defines that the object must have both members x and y.

Next, let’s create a JSON document:

SET @json_doc = '{"x": 1, "y": 2}';

Then, let’s validate the JSON document according to the JSON schema:

SELECT JSON_SCHEMA_VALID(@schema, @json_doc);

+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @json_doc) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

This means that @json_doc meets the definition of @schema.

If we change the JSON document by removing the member y from the object:

SET @json_doc = '{"x": 1}';

Let’s validate it again:

SELECT JSON_SCHEMA_VALID(@schema, @json_doc);

+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @json_doc) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

Here, the schema requires y, so JSON_SCHEMA_VALID() returns 0 when it is missing.

In addition to that, you can also use JSON_SCHEMA_VALID() in CHECK constraints for JSON fields.