MySQL JSON_SCHEMA_VALIDATION_REPORT

Summary: in this tutorial, you will learn how to use the MySQL JSON_SCHEMA_VALIDATION_REPORT() function to validate JSON documents against a specified schema.

Introduction to MySQL JSON_SCHEMA_VALIDATION_REPORT() function

The JSON_SCHEMA_VALIDATION_REPORT() function validates JSON documents against a specified JSON schema and provides a detailed report on validation failures.

Here’s the syntax of the JSON_SCHEMA_VALIDATION_REPORT() function:

JSON_SCHEMA_VALIDATION_REPORT(json_schema, json_document)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_schema: The JSON schema against which you want to validate the JSON document.
• json_document: The JSON document that you want to validate.

If the JSON document is valid based on the JSON Schema, the JSON_SCHEMA_VALIDATION_REPORT() function returns a JSON object with one property valid having the value true.

If the JSON document fails validation, the JSON_SCHEMA_VALIDATION_REPORT() function returns a JSON object that includes the following properties:

• valid: with the value false.
• reason: contains the reason for the failure.
• schema-location: A JSON pointer URI fragment identifies the precise location within the JSON schema where the validation encountered an issue.
• document-location: A JSON pointer URI fragment specifies the exact location within the JSON document where the validation encountered an error.
• schema-failed-keyword: A string naming the specific keyword or property in the JSON schema that was violated.

MySQL JSON_SCHEMA_VALIDATION_REPORT() function examples

Let’s take an example of using the JSON_SCHEMA_VALIDATION_REPORT() function.

First, set up a JSON schema for employee details:

SET @employee_schema = '{
    "type": "object",
    "properties": {
        "name": {"type": "string"},
        "age": {"type": "integer"},
        "department": {"type": "string"}
    },
    "required": ["name", "age", "department"]
}';Code language: SQL (Structured Query Language) (sql)

Second, validate a JSON document against the schema:

SET @valid_employee = '{
    "name": "John",
    "age": 30,
    "department": "HR"
}';

SELECT JSON_SCHEMA_VALIDATION_REPORT(@employee_schema, @valid_employee) result;Code language: SQL (Structured Query Language) (sql)

It returns a JSON object that has the valid property with the value true because the JSON document is valid according to the schema:

+-----------------+
| result          |
+-----------------+
| {"valid": true} |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Third, use the JSON_SCHEMA_VALIDATION_REPORT() function to validate the invalid JSON document:

SET @invalid_employee = '{
    "name": "Alice",
    "age": "25",  
    "department": "IT"
}';

SELECT 
  JSON_PRETTY(
    JSON_SCHEMA_VALIDATION_REPORT(
      @employee_schema, @invalid_employee
    )
  ) result;Code language: SQL (Structured Query Language) (sql)

Note that we use the JSON_PRETTY() function to pretty-print the output for better readability.

It returns the following output:

-------------------------------------------------------------+
| result                                                                                                                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "valid": false,
  "reason": "The JSON document location '#/age' failed requirement 'type' at JSON Schema location '#/properties/age'",
  "schema-location": "#/properties/age",
  "document-location": "#/age",
  "schema-failed-keyword": "type"
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output explains the reason for failure in the reason object that the type of the age is not valid according to the schema.

Summary

• Use the JSON_SCHEMA_VALIDATION_REPORT() function to validate JSON documents against a specified JSON schema.