JSON_SCHEMA_VALIDATION_REPORT

Purpose

JSON_SCHEMA_VALIDATION_REPORT() returns a detailed report on the validation.

Syntax

JSON_SCHEMA_VALIDATION_REPORT(schema, document)

Description

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

  • JSON schemas support the required attribute to enforce the inclusion of required properties.

  • JSON schemas support the id, $schema, description, and type attributes but do not require any of these.

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

• If any of the arguments is not valid JSON, the function raises an error.

Different from JSON_SCHEMA_VALID(), the JSON_SCHEMA_VALIDATION_REPORT() function returns a detailed report, which is a JSON object, on the validation. If the JSON document validates against the JSON schema, the function returns a JSON object with the property valid having the value true. Otherwise, the function returns a non-empty JSON object with details of the validation failure.

The validation failure information mainly contains the following properties:

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

• reason: the reason for the validation failure.

• schema-location: the location of the validation failure in the JSON schema.

• document-location: the location of the validation failure in the JSON document.

• schema-failed-keyword: the keyword or property that was violated in the JSON schema.

Examples

• The following example defines the JSON schema '{"type": "string"}', which requires that valid JSON data be of the string type. '"JSON_doc"' is a valid JSON string, which conforms to the requirement of the JSON schema.

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

• The following example defines a JSON schema requiring that the age property in valid JSON data 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 where the age property is set to 17, which does not conform to the requirement of the JSON schema.

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

  Call JSON_SCHEMA_VALIDATION_REPORT() to get the validation report.

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