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.

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

Previous MySQL Exercise: Convert Row Data to JSON Using JSON_OBJECT.
Next MySQL Exercise: Update Multiple Values in a JSON Column Using JSON_SET.