JSON_SCHEMA_VALIDATION_REPORT

Description

This function returns a detailed validation report.

Syntax

JSON_SCHEMA_VALIDATION_REPORT(schema, document)

Remarks

• The schema parameter specifies the JSON schema, which must be a valid JSON object.

  • The required attribute is supported in the JSON schema to indicate the required attributes.

  • The id, $schema, description, and type attributes are supported in the JSON schema, but they are not required.

• The document parameter specifies the JSON document to be validated, which must be a valid JSON document.

• If either parameter is not a valid JSON object, an error is returned.

Unlike the JSON_SCHEMA_VALID() function, the JSON_SCHEMA_VALIDATION_REPORT() function returns a detailed validation report in the form of a JSON object. If the JSON document conforms to the JSON schema, the function returns a JSON object with the valid attribute set to true. If the JSON document does not conform to the JSON schema, the function returns a non-empty JSON object that contains detailed information about the validation failure.

The detailed information about the validation failure includes the following attributes:

• valid: The value is always false when the validation fails.

• reason: The reason for the validation failure.

• schema-location: The location in the JSON schema where the validation failed.

• document-location: The location in the JSON document where the validation failed.

• schema-failed-keyword: The keyword or attribute name in the JSON schema that violates the specification.

Examples

• The following example defines a JSON schema using '{"type": "string"}', indicating that the valid JSON data should be of the string type. '"JSON_doc"' is a valid JSON string that conforms to the string type specified in the schema.

  SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "string"}', '"JSON_doc"');

  The return result is as follows:

  +-------------------------------------------------------------------+
  | JSON_SCHEMA_VALIDATION_REPORT('{"type": "string"}', '"JSON_doc"') |
  +-------------------------------------------------------------------+
  | {"valid": true}                                                   |
  +-------------------------------------------------------------------+
  1 row in set (0.001 sec)

• The following example defines a JSON schema that requires the age attribute to be an integer greater than or equal to 18.

  SET @schema = '{"type": "object", "properties": {"age": {"type": "integer", "minimum": 18}}, "required": ["age"]}';

  Define a JSON document with an age value of 17, which does not conform to the JSON schema.

  SET @document = '{"age": 17}';

  Use the JSON_SCHEMA_VALIDATION_REPORT() function to obtain the validation report.

  SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);

  The return result is as follows:

  +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)                                                                                                                                                                                                |
  +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | {"valid": false, "reason": "The JSON document location '#/age' failed requirement 'minimum' at JSON Schema location '#/properties/age", "schema-location": "#/properties/age", "document-location": "#/age", "schema-failed-keyword": "minimum"} |
  +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.001 sec)