Aggregating Data into JSON Arrays

Aggregate Data into a JSON Array with JSON_ARRAYAGG

Write a MySQL query to aggregate multiple row values into a JSON array using the JSON_ARRAYAGG function.

Solution:

-- Aggregate product names into a JSON array.

-- Use JSON_ARRAYAGG to aggregate all product names into a single JSON array.
SELECT 
    -- JSON_ARRAYAGG collects all values of ProductName and formats them as a JSON array.
    JSON_ARRAYAGG(ProductName) AS ProductNames  -- Alias the resulting JSON array as ProductNames.

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

Explanation:

• Purpose of the Query:
• To combine multiple row values into a single JSON array.
• Demonstrates the use of JSON_ARRAYAGG for aggregation of semi-structured data.
• Key Components:
• JSON_ARRAYAGG(ProductName) : Aggregates all product names into an array.
• AS ProductNames : Renames the output column for clarity.
• Real-World Application:
• Useful in scenarios where a list of items is needed in JSON format, such as in API responses.

Notes:

• This function is especially beneficial when returning data for client-side processing.
• Ensure the aggregated column contains compatible data types for JSON conversion.

For more Practice: Solve these Related Problems:

• Write a MySQL query to aggregate multiple rows into a JSON array of JSON objects.
• Write a MySQL query to aggregate filtered row values into a JSON array based on a specific condition.
• Write a MySQL query to aggregate and sort data before converting the results into a JSON array.
• Write a MySQL query to group data and then aggregate each group into its own JSON array.

Go to:

• MySQL JSON and NoSQL Features Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Convert Row Data to JSON Using JSON_OBJECT.
NEXT : Update Multiple Values in a JSON Column Using JSON_SET.

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