Aggregate JSON arrays

Given: a table with JSON arrays

CREATE TABLE t (id int auto_increment primary key, d json);
INSERT INTO t VALUES (1, '["apple", "apple", "orange"]');
INSERT INTO t VALUES (2, '["apple", "banana", "orange", "orange", "orange"]');

The desired output is each row with a count of the unique objects:

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

JSON_TABLE() can transform the array into rows.

SELECT id, fruit
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt;

+----+--------+
| id | fruit  |
+----+--------+
|  1 | apple  |
|  1 | apple  |
|  1 | orange |
|  2 | apple  |
|  2 | banana |
|  2 | orange |
|  2 | orange |
|  2 | orange |
+----+--------+

(If you got an error there about either permissions or an unknown table/column in the JSON_TABLE, upgrade to at least 8.0.14 to get past some bugs.)

We can then use the ordinary aggregation functions:

SELECT
    id,
    fruit,
    COUNT(*)
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt
GROUP BY id, fruit;

+----+--------+----------+
| id | fruit  | COUNT(*) |
+----+--------+----------+
|  1 | apple  |        2 |
|  1 | orange |        1 |
|  2 | apple  |        1 |
|  2 | banana |        1 |
|  2 | orange |        3 |
+----+--------+----------+

With a subquery or a common table expression, we can aggregate individual rows at a time (meaning you can also select other columns from t). Then JSON_OBJECTAGG will combine those results into a single object.

SELECT
    id,
    (
        WITH cte AS (
            SELECT fruit, COUNT(*) AS c
            FROM JSON_TABLE(d, 
                "$[*]" COLUMNS (
                    fruit VARCHAR(100) PATH "$"
                )
            ) AS dt
            GROUP BY fruit
        )
        SELECT JSON_OBJECTAGG(fruit, c) FROM cte
    ) AS fruitCount
FROM t;

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

et voilà