MySQL JSON_SCHEMA_VALIDATION_REPORT() Function

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

JSON_SCHEMA_VALIDATION_REPORT() Syntax

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

JSON_SCHEMA_VALIDATION_REPORT(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_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.

JSON_SCHEMA_VALIDATION_REPORT() Examples

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": "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
        }
      }
    }';

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_VALIDATION_REPORT(@schema, @json_doc) AS Report\G

*************************** 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:

SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @json_doc)) AS Report\G

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