JSON_SCHEMA_VALIDATION_REPORT

Purpose

This function returns a detailed validation report.

Syntax

JSON_SCHEMA_VALIDATION_REPORT(schema, document)

Parameters

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

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

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

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

• If one of the parameters is not a valid JSON, 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 specification, the function returns a JSON object with the valid attribute set to true. If the JSON document does not conform to the JSON schema specification, the function returns a non-empty JSON object that contains detailed information about the validation failure.

The validation failure information mainly includes the following attributes:

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

• reason: Displays the reason for the validation failure.

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

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

• schema-failed-keyword: Displays 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 valid JSON data should be a string. '"JSON_doc"' is a valid JSON string that conforms to the specified string type.

  obclient [infotest]> SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type": "string"}', '"JSON_doc"');
  

  The result is as follows:

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

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

  obclient [infotest]> 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 specification.

  obclient [infotest]> SET @document = '{"age": 17}';
  

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

  obclient [infotest]> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
  

  The 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