How the JSON_SCHEMA_VALID() function works in Mariadb?

The JSON_SCHEMA_VALID() function is a built-in function in Mariadb that allows you to validate a JSON document against a JSON schema. It can be useful when you want to ensure that the JSON data conforms to a certain structure and format.

Syntax

The syntax of the JSON_SCHEMA_VALID() function is as follows:

JSON_SCHEMA_VALID(json_schema, json_doc)

The function takes two arguments, as follows:

• json_schema: This is the JSON schema to validate against. It must be a valid JSON schema or a column that contains JSON schemas.
• json_doc: This is the JSON document to validate. It must be a valid JSON document or a column that contains JSON documents.

The function returns 1 if the JSON document is valid according to the JSON schema, or 0 otherwise.

Examples

Example 1: Validating a JSON document with a simple JSON schema

In this example, we use the JSON_SCHEMA_VALID() function to validate a JSON document with a simple JSON schema.

SELECT JSON_SCHEMA_VALID(
  '{"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "number"}}}',
  '{"name": "Alice", "age": 25}'
) AS is_valid;

The output is:

+----------+
| is_valid |
+----------+
|        1 |
+----------+

As you can see, the function returns 1 because the JSON document is valid according to the JSON schema. The JSON schema specifies that the JSON document must be an object with two properties: name and age. The name property must be a string, and the age property must be an integer.

Example 2: Validating a JSON document with an invalid JSON schema

In this example, we use the JSON_SCHEMA_VALID() function to validate a JSON document with an invalid JSON schema.

SELECT JSON_SCHEMA_VALID(
  '{"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "number"}}}',
  '{"name": "Alice", "age": "25"}'
) AS is_valid;

The output is:

+----------+
| is_valid |
+----------+
|        0 |
+----------+

As you can see, the function returns 0 because the JSON document is not valid according to the JSON schema. The JSON schema specifies that the age property must be an integer, but the JSON document has a string value for the age property.

Example 3: Validating a JSON document with a complex JSON schema

In this example, we use the JSON_SCHEMA_VALID() function to validate a JSON document with a complex JSON schema.

SELECT JSON_SCHEMA_VALID(
  '{"type": "object", "properties": {"name": {"type": "string"}, "age": {"type": "number", "minimum": 18}, "gender": {"type": "string", "enum": ["male", "female"]}, "hobbies": {"type": "array", "items": {"type": "string"}, "minItems": 1}}, "required": ["name", "age", "gender"]}',
  '{"name": "Alice", "age": 25, "gender": "female", "hobbies": ["reading", "writing", "coding"]}'
) AS is_valid;

The output is:

+----------+
| is_valid |
+----------+
|        1 |
+----------+

As you can see, the function returns 1 because the JSON document is valid according to the JSON schema. The JSON schema specifies that the JSON document must be an object with four properties: name, age, gender, and hobbies. The name property must be a string, the age property must be an integer greater than or equal to 18, the gender property must be a string that is either male or female, and the hobbies property must be an array of strings with at least one element. The name, age, and gender properties are also required, meaning they cannot be missing or null.

Related Functions

There are some other functions in Mariadb that are related to the JSON_SCHEMA_VALID() function. Here are some of them:

• JSON_VALID() function: This function checks whether a string is a valid JSON document. It takes a string as an argument, and returns 1 if the string is a valid JSON document, or 0 otherwise. For example, JSON_VALID('{"a": "b", "c": "d"}') returns 1.

Conclusion

The JSON_SCHEMA_VALID() function is a useful function in Mariadb that allows you to validate a JSON document against a JSON schema. It can handle different types of JSON documents and JSON schemas, and returns 1 if the JSON document is valid according to the JSON schema, or 0 otherwise. There are also some other functions that are related to the JSON_SCHEMA_VALID() function, such as JSON_SCHEMA_VALIDATION_REPORT(), JSON_SCHEMA_VALIDATION_ERRORS(), and JSON_VALID(). You can use these functions to manipulate JSON data in different ways.