The JSON_LENGTH function in MySQL is used to find the number of elements in a JSON array or the number of keys in a JSON object. This function helps you retrieve the length of a JSON array or the number of keys in a JSON object within a MySQL database.

The JSON_LENGTH function can be used in various scenarios, including:

Calculating the size of JSON data stored in MySQL tables.
Filtering data based on the length of specific JSON values.
Optimizing queries that involve JSON data by identifying and eliminating excessively large JSON documents.

Syntax

Here is the syntax for the JSON_LENGTH function:

JSON_LENGTH(json_doc[, path])

json_doc: This is the JSON document or expression for which you want to determine the length.
path (optional): This parameter is used to specify the path to the value within the JSON document. If omitted, the function returns the length of the entire JSON document.

Example

Now, let’s look at an example to illustrate the usage of the JSON_LENGTH function:

Consider a table named employees with a column named details that stores JSON data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    details JSON
);

INSERT INTO employees VALUES
(1, 'John Doe', '{"skills": ["Java", "MySQL", "Python"], "experience": {"years": 5, "position": "Software Engineer"}}'),
(2, 'Jane Smith', '{"skills": ["C++", "SQL", "JavaScript"], "experience": {"years": 8, "position": "Senior Developer"}}');

Now, let’s use the JSON_LENGTH function to find the length of the “skills” array for each employee:

SELECT id, name,
JSON_LENGTH(details, '$.skills') AS skills_length
FROM employees;

In this example, the JSON_LENGTH function is used to find the length of the “skills” array within the “details” column. The ‘$’ specifies the root of the JSON document, and ‘$.skills’ specifies the path to the “skills” array within the JSON document.

The result of the query would be:

+----+------------+--------------+
| id | name       | skills_length|
+----+------------+--------------+
| 1  | John Doe   | 3            |
| 2  | Jane Smith | 3            |
+----+------------+--------------+

This indicates that John Doe and Jane Smith both have an array of three skills each.

The JSON_LENGTH function is a useful tool for working with JSON data in MySQL. It allows you to quickly and easily determine the length of a JSON document or a specific value within the document.