The JSON_SCHEMA_VALID function in MySQL is used to validate a JSON document against a JSON schema. It is a valuable tool for ensuring the integrity and consistency of JSON data stored in MySQL databases.

The primary purpose of the JSON_SCHEMA_VALID function is to enforce data validation rules within MySQL. By comparing a JSON document to a JSON schema, it verifies that the document adheres to the defined structure, type constraints, and other specifications. This helps maintain data quality and consistency within the database.

Syntax

The JSON_SCHEMA_VALID function takes two arguments:

JSON_SCHEMA_VALID(schema JSON, document JSON)

schema: The JSON schema that defines the structure and constraints for the JSON document.
document: The JSON document that is to be validated against the schema.

The JSON_SCHEMA_VALID function returns a Boolean value:
1: The JSON document validates against the schema.
0: The JSON document does not validate against the schema.

The JSON_SCHEMA_VALID function raises errors if either of the arguments is not valid JSON or if the schema is not a valid JSON object.

Example

Here is an example of how to use the JSON_SCHEMA_VALID function to validate a JSON document against a JSON schema:

CREATE TABLE products (
  id INT PRIMARY KEY,
  data JSON
);

INSERT INTO products 
VALUES (1, '{"name": "Coffee", "price": 5.00}');

SELECT 
JSON_SCHEMA_VALID('{"type": "object", "properties": {"name": {"type": "string"}, "price": {"type": "number"}}}', data) AS is_valid 
FROM products;

This query will return the following results:

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

This indicates that the JSON document stored in the products table validates against the JSON schema.

Using JSON_SCHEMA_VALID() with CHECK constraints

The JSON_SCHEMA_VALID() function can also be used to enforce CHECK constraints on JSON columns in MySQL tables. This can help to ensure that only valid JSON data is inserted into the table.

To use the JSON_SCHEMA_VALID() function with a CHECK constraint, you must pass the JSON schema to the function inline. For example, the following CREATE TABLE statement creates a table with a CHECK constraint that validates the data column against a JSON schema:

CREATE TABLE products (
  id INT PRIMARY KEY,
  data JSON,
  CHECK (JSON_SCHEMA_VALID('{"type": "object", "properties": {"name": {"type": "string"}, "price": {"type": "number"}}}', data))
);

This constraint ensures that only JSON documents that meet the specified schema can be inserted into the data column.

Conclusion

The JSON_SCHEMA_VALID function is a valuable tool for developers working with JSON data in MySQL. It provides a way to ensure that JSON documents are valid and consistent with the expected structure and constraints. This can help to prevent errors and data corruption, and it can also make it easier to automate data validation tasks.