The JSON_VALUE function in MySQL is used to extract a scalar value from a JSON document. It allows you to retrieve a specific value from a JSON string based on a specified JSON path expression. This function is part of MySQL’s support for JSON, which enables you to work with JSON data within the relational database.

Syntax

Here is the syntax for the JSON_VALUE function:

JSON_VALUE(json_document, path_expression [RETURNING data_type]);

json_document: The JSON string or JSON column from which you want to extract a value.
path_expression: A JSON path expression specifying the location of the value you want to extract.
data_type (optional): The data type to which you want to cast the result. If omitted, the result is returned as a string.

Example

Now, let’s look at an example to illustrate how the JSON_VALUE function works:

Suppose you have a table called employee with a column named info that stores employee information in JSON format:

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    info JSON
);

INSERT INTO employee (id, name, info) VALUES
(1, 'John Doe', '{"age": 30, "position": "Software Engineer", "skills": ["Java", "MySQL"]}'),
(2, 'Jane Smith', '{"age": 25, "position": "Data Analyst", "skills": ["SQL", "Python"]}'),
(3, 'Bob Johnson', '{"age": 35, "position": "UI/UX Designer", "skills": ["HTML", "CSS"]}');

Now, let’s use the JSON_VALUE function to extract the age of each employee:

SELECT id, name,
JSON_VALUE(info, '$.age') AS age
FROM employee;

In this example, the JSON_VALUE function is used to extract the age from the info column using the JSON path expression $.age. The result set would look like this:

+----+-------------+-----+
| id | name        | age |
+----+-------------+-----+
| 1  | John Doe    | 30  |
| 2  | Jane Smith  | 25  |
| 3  | Bob Johnson | 35  |
+----+-------------+-----+

In this result, the age of each employee is successfully extracted using the JSON_VALUE function. You can customize the path expression based on the structure of your JSON data to retrieve different values.

Conclusion

The MySQL JSON_VALUE function is a useful feature for working with JSON data in MySQL databases. It allows you to extract specific values from JSON documents, convert them to desired data types, and handle potential errors gracefully. This function simplifies JSON processing and makes it easier to integrate JSON data into MySQL applications.