The JSON_EXTRACT function in MySQL is used to extract data from a JSON document. It allows you to retrieve a specific value or object from a JSON string based on a given JSON path expression. This function is particularly useful when dealing with JSON data stored in a MySQL database.

Syntax

JSON_EXTRACT(json_document, path)

json_document: The JSON string or JSON column from which you want to extract data.
path: The JSON path expression that specifies the location of the data you want to extract.

Example

Let’s consider a table named employees with a column info containing JSON data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    info JSON
);

INSERT INTO employees VALUES
(1, '{"name": "John", "age": 30, "department": {"name": "IT", "location": "New York"}}'),
(2, '{"name": "Alice", "age": 25, "department": {"name": "Marketing", "location": "Los Angeles"}}'),
(3, '{"name": "Bob", "age": 35, "department": {"name": "HR", "location": "Chicago"}}');

Now, let’s use the JSON_EXTRACT function to extract specific information from the info column:

Extracting the name of the first employee:

SELECT JSON_EXTRACT(info, '$.name') AS employee_name
FROM employees
WHERE id = 1;

Output: "John"

Extracting the department name and location of the second employee:

SELECT 
JSON_EXTRACT(info, '$.department.name') AS department_name,
JSON_EXTRACT(info, '$.department.location') AS department_location
FROM employees
WHERE id = 2;

Output:

+------------------+-----------------------+
| department_name  | department_location   |
+------------------+-----------------------+
| "Marketing"      | "Los Angeles"         |
+------------------+-----------------------+

In these examples, the JSON path expressions (‘$.name’, ‘$.department.name’, ‘$.department.location’) are used to navigate through the JSON structure and extract the desired information. The JSON_EXTRACT function is flexible and can handle a variety of JSON path expressions to suit your specific data extraction needs.

In summary, the JSON_EXTRACT function and its shorthand operators are powerful tools for manipulating and extracting data from JSON documents stored in MySQL databases. They provide a convenient way to navigate through nested structures and retrieve specific values for data analysis and application development.