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


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

JSON_SCHEMA_VALID(schema, json_doc)


Required. The JSON schema that must be a valid JSON object.
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 a failed verification.

This function will return NULL if any parameter is NULL.


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": "",
      "$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, Since the JSON schema defines that y is a required member, so the JSON_SCHEMA_VALID() function returns 0.

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