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à