MySQL JSON_SCHEMA_VALID() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_SCHEMA_VALID() function to validate a JSON document against a JSON schema.

Introduction to MySQL JSON_SCHEMA_VALID() function

A JSON schema is a document that describes the structure of JSON data. It defines the data types, constraints, and relationships between different parts of the JSON document. In short, it serves as a blueprint for validating the structure of a JSON document.

In MySQL, the JSON_SCHEMA_VALID() function allows you to check if a given JSON document is valid against a specified JSON schema. It returns true (1) if the JSON document is valid according to the schema and false (0) otherwise.

Here’s the syntax of the JSON_SCHEMA_VALID() function:

JSON_SCHEMA_VALID(json_schema, json_document)Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_schema: This is the JSON schema against which the document is validated.
json_document: This is the JSON document you want to validate.

MySQL JSON_SCHEMA_VALID() function example

Let’s take a simple example to illustrate how to use JSON_SCHEMA_VALID() function.

First, create a new table called employees:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    details JSON
);Code language: SQL (Structured Query Language) (sql)

Second, insert data into the employees table:

INSERT INTO employees (details) 
VALUES ('{"name": "John", "age": 30, "department": "HR"}'),
       ('{"name": "Alice", "age": 25, "department": "IT"}');Code language: SQL (Structured Query Language) (sql)

The following defines a JSON schema that specifies the expected structure for employee details:

SET @employee_schema = '{
    "type": "object",
    "properties": {
        "name": {"type": "string"},
        "age": {"type": "integer"},
        "department": {"type": "string"}
    },
    "required": ["name", "age", "department"]
}';Code language: SQL (Structured Query Language) (sql)

The schema defines an object with three required properties: name (string), age (integer), and department (string).

Now, you can use JSON_SCHEMA_VALID() function to check if the stored JSON documents adhere to the defined schema:

SELECT 
  id, 
  JSON_SCHEMA_VALID(@employee_schema, details) AS is_valid 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

This query returns a result set with the employee IDs and a boolean indicating whether each employee’s details are valid according to the specified schema:

+----+----------+
| id | is_valid |
+----+----------+
|  1 |        1 |
|  2 |        1 |
+----+----------+
2 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The following attempts to insert an employee who doesn’t have the age information:

INSERT INTO employees (details) 
VALUES ('{"name": "Bob", "department": "Finance"}');Code language: SQL (Structured Query Language) (sql)

Now, when you run the validation query again, you’ll see that the new entry is marked as invalid:

SELECT 
  id, 
  JSON_SCHEMA_VALID(@employee_schema, details) AS is_valid 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+
| id | is_valid |
+----+----------+
|  1 |        1 |
|  2 |        1 |
|  3 |        0 |
+----+----------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON_SCHEMA_VALID() function to validate JSON documents against a specified schema.

Was this tutorial helpful?