In MySQL, the JSON_SCHEMA_VALIDATION_REPORT() function validates a JSON document against a specified JSON schema and returns a validation report.


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



Required. The JSON schema that must be a valid JSON object.
Required. The JSON document to validate.

Return value

The JSON_SCHEMA_VALIDATION_REPORT() function returns a report of a validation result, which is a JSON object. This report includes the following members:

  • valid: true indicates that the JSON document has passed the verification, and false indicates that the JSON document has failed the verification.
  • reason: The reason for the failure.
  • schema-location: A JSON pointer URI fragment identifier indicating where in the JSON schema the validation failed
  • document-location: A JSON pointer URI fragment identifier indicating where in the JSON document the validation failed
  • schema-failed-keyword: A string containing the name of the keyword or property in the JSON schema that was violated

If the JSON document passes validation, there is only one member valid: true in the report . Other members will only appear in the report of a failed validation.

This function will return NULL if any parameter is NULL.

If schema is not a JSON object or json_doc is not a valid JSON document, MySQL will give an error message.


This example shows how to use the JSON_SCHEMA_VALIDATION_REPORT() function 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

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:

*************************** 1\. row ***************************
Report: {"valid": true}

Here, the report {"valid": true} indicates that the JSON document meets the JSON schema.

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

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

Let’s validate it again:

*************************** 1\. row ***************************
Report: {
  "valid": false,
  "reason": "The JSON document location '#/y' failed requirement 'maximum' at JSON Schema location '#/properties/y'",
  "schema-location": "#/properties/y",
  "document-location": "#/y",
  "schema-failed-keyword": "maximum"

Since the value 200 of y does not meet the definition -180 to 180, so it fails.

Note: we use the JSON_PRETTY() function to format the report.