Calculating JSON Array Length in MySQL

Extract JSON Array Length Using JSON_LENGTH

Write a MySQL query to determine the number of elements in a JSON array stored in the Reviews column of the Products table using the JSON_LENGTH function.

Solution:

-- Retrieve the count of elements in the JSON array in the Reviews column.

-- Select the ProductName column and calculate the length of the JSON array in the Reviews column.
SELECT 
    -- Retrieve the ProductName column.
    ProductName, 
    
    -- Use JSON_LENGTH to count the number of elements in the JSON array stored in the Reviews column.
    -- JSON_LENGTH returns the number of elements in a JSON array or keys in a JSON object.
    JSON_LENGTH(Reviews) AS ReviewCount  -- Alias the result as ReviewCount for clarity.

-- Specify the table from which to retrieve the data.
FROM Products;

Explanation:

• Purpose of the Query:
• To calculate the number of elements in a JSON array stored in a column.
• Demonstrates how to use JSON_LENGTH for array-based JSON data.
• Key Components:
• JSON_LENGTH(Reviews) : Counts the number of items in the JSON array.
• AS ReviewCount : Assigns an alias to the result for clarity.
• Real-World Application:
• Helps in analytics to understand customer feedback volume or review counts per product.

Notes:

• Ensure that the Reviews column stores a valid JSON array.
• If the JSON document is not an array, JSON_LENGTH may return NULL.

For more Practice: Solve these Related Problems:

• Write a MySQL query to filter rows where the JSON array length in a column exceeds a specified number.
• Write a MySQL query to extract the length of a nested JSON array within a JSON document.
• Write a MySQL query to compute the JSON array length and order the results based on that length.
• Write a MySQL query to return rows where the JSON array length exactly matches a given value.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Replace a Value in a JSON Document using JSON_REPLACE.
Next MySQL Exercise: Create a Table with a JSON Column.