Purpose
This function is used to validate a JSON document. If the JSON document complies with the JSON Schema specification, it returns 1. If it does not comply, it returns 0. It can be used for column constraints.
Syntax
JSON_SCHEMA_VALID(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 these attributes are not required.
The
documentparameter specifies the JSON document to be validated. This JSON document must be valid.If any one of the parameters is not valid JSON, the function throws an error.
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 the JSON document to be validated.
Notice
In this statement, the string JSON_doc is enclosed in double quotes ("") to indicate that it is a valid JSON string, not just a regular string. In JSON, string values must be enclosed in double quotes.
obclient [infotest]> SELECT JSON_SCHEMA_VALID('{"type": "string"}', '"JSON_doc"');
The return result is as follows:
+-------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "string"}', '"JSON_doc"') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set
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 a successful validation.