Description
This function is used to return a detailed verification report.
Syntax
JSON_SCHEMA_VALIDATION_REPORT(schema, document)
Description
The
schemaparameter specifies the JSON Schema, which must be a valid JSON object.The
requiredattribute can be used in a JSON Schema to specify that an attribute is mandatory.The JSON Schema supports the
id,$schema,description, andtypeattributes, but their presence is not mandatory.
The
documentparameter specifies the JSON document to be validated. This JSON document must be valid.If any one of the parameters is not a valid JSON object, this function throws an error.
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 complies with the JSON Schema specification, this function returns a JSON object with a valid property set to true. If the JSON document does not comply with the JSON Schema specification, the function returns a non-empty JSON object containing detailed information about the validation failures.
Information about failed verification mainly includes the following attributes:
valid: This value is alwaysfalsewhen the validation fails.reason: The reason for the verification failure.schema-location: Indicates the location where the validation failed in the JSON Schema.document-location: Indicates the location in the JSON document where the validation failed.schema-failed-keyword: Displays the keywords or attribute names that violate the specifications in the JSON Schema.
Examples
In the following example,
'{"type": "string"}'defines a JSON Schema that specifies valid JSON data must be of the string type.'"JSON_doc"'is a valid JSON string that conforms to the string type specified by the 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 setIn the following example, a JSON Schema is defined that requires its
ageattribute to be an integer greater than or equal to18.obclient [infotest]> SET @schema = '{"type": "object", "properties": {"age": {"type": "integer", "minimum": 18}}, "required": ["age"]}';Define a JSON document whose
agevalue is17, which does not comply with 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 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