Aggregating Rows into JSON Arrays of Objects

Using JSON_ARRAYAGG to Convert Data to JSON in MySQL

Write a MySQL query to aggregate multiple rows of product information into a single JSON array of objects using JSON_ARRAYAGG.

Solution:

-- Aggregate product details into a JSON array of objects.

-- Use JSON_ARRAYAGG to aggregate product details into a single JSON array.
-- Each product's details are formatted as a JSON object using JSON_OBJECT.
SELECT 
    -- JSON_ARRAYAGG collects all JSON objects created for each product into a single JSON array.
    JSON_ARRAYAGG(
        -- JSON_OBJECT creates a JSON object for each product with specified key-value pairs.
        JSON_OBJECT(
            'ProductID', ProductID,    -- Include the ProductID column as a key-value pair.
            'ProductName', ProductName, -- Include the ProductName column as a key-value pair.
            'Price', Price             -- Include the Price column as a key-value pair.
        )
    ) AS ProductsJSON  -- Alias the resulting JSON array as ProductsJSON.

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

Explanation:

• Purpose of the Query:
• To convert multiple rows of data into a structured JSON array of objects.
• Demonstrates how JSON_ARRAYAGG combined with JSON_OBJECT can format data for API responses.
• Key Components:
• JSON_OBJECT(...) : Constructs a JSON object for each row.
• JSON_ARRAYAGG(...) : Aggregates these objects into a single JSON array.
• Real-World Application:
• Ideal for creating JSON responses in web applications and APIs that require formatted data.

Notes:

• The approach streamlines the conversion of relational data into a JSON format.
• Ensure that the columns used in JSON_OBJECT exist and are of appropriate data types.

For more Practice: Solve these Related Problems:

• Write a MySQL query to aggregate rows into a JSON array of objects with nested JSON arrays for detailed attributes.
• Write a MySQL query to convert selected columns into a JSON array of objects and filter by a specific condition.
• Write a MySQL query to group data and then aggregate each group into a JSON array of objects using custom key names.
• Write a MySQL query to aggregate multiple rows into a JSON array of objects while excluding null values from the output.

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

Previous MySQL Exercise: Append Data to a JSON Array Using JSON_ARRAY_APPEND.
Next MySQL Exercise: Query and Unquote JSON Data using JSON_UNQUOTE.