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() 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 a failed verification.

This function will return NULL if any parameter is NULL.

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, 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.