JSON_SCHEMA_VALID

Description

This function is used to validate a JSON document. It returns 1 if the JSON document conforms to the JSON Schema specification, and 0 otherwise. It can be used for column constraints.

Syntax

JSON_SCHEMA_VALID(schema, document)

Description

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

  • The required attribute is supported in JSON Schema to enforce the inclusion of specific 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, which must be a valid JSON document.

• If either parameter is not a valid JSON, the function will raise an error.

Examples

In the following example, a JSON Schema is defined using '{"type": "string"}', indicating that valid JSON data should be of the string type. '"JSON_doc"' is the JSON document to be validated.

tip

In this statement, the string JSON_doc is enclosed in double quotes ("), indicating that it is a valid JSON string, not just a regular string. In JSON, string values must be enclosed in double quotes.

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

The result is as follows:

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

In this example, since the JSON document is a valid JSON string that conforms to the string type specified by the schema, the function returns 1, indicating that the validation was successful.