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 | …