GROUP_CONCAT and JSON

GROUP_CONCAT and Aggregating into JSON

GROUP_CONCAT([DISTINCT] expr [ORDER BY ...] [SEPARATOR str]) joins a group's values into a string. JSON_ARRAYAGG(expr) and JSON_OBJECTAGG(key, value) build the JSON a PHP endpoint returns:

Each category's products as a string, a JSON array and a JSON objectSQL

SELECT c.name AS category, GROUP_CONCAT(p.id ORDER BY p.price DESC) AS by_price,
       JSON_ARRAYAGG(p.id) AS ids, JSON_OBJECTAGG(p.sku, p.stock) AS stock
FROM categories AS c JOIN products AS p ON p.category_id = c.id
GROUP BY c.id ORDER BY c.id;

Output

+-------------+----------+-----------+----------------------------------------------------+
| category    | by_price | ids       | stock                                              |
+-------------+----------+-----------+----------------------------------------------------+
| Programming | 4,6,1    | [1, 4, 6] | {"BK-LAR-01": 18, "BK-LNX-01": 9, "BK-PHP-01": 25} |
| Databases   | 2,3      | [2, 3]    | {"BK-SQL-01": 12, "BK-SQL-02": 0}                  |
| Design      | 5        | [5]       | {"BK-UX-01": 7}                                    |
| Accessories | 7,8      | [7, 8]    | {"AC-MUG-01": 60, "AC-STK-01": 150}                |
+-------------+----------+-----------+----------------------------------------------------+
4 rows in set (0.000 sec)

Only GROUP_CONCAT takes ORDER BY; the array's order is undefined, and JSON_ARRAYAGG(id ORDER BY id) is syntax error 1064. JSON_OBJECTAGG keeps a duplicate key's last value. GROUP_CONCAT silently stops at group_concat_max_len (1,024 bytes by default, warning 1260), so raise it per session.

MENU