How to calculate count of each value in MySQL JSON array?

Question

I have a MySQL table with the following definition:

mysql> desc person;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | PRI | NULL    |       |
| name   | text    | YES  |     | NULL    |       |
| fruits | json    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

The table has some sample data as follows:

mysql> select * from person;
+----+------+----------------------------------+
| id | name | fruits                           |
+----+------+----------------------------------+
|  1 | Tom  | ["apple", "orange"]              |
|  2 | John | ["apple", "mango"]               |
|  3 | Tony | ["apple", "mango", "strawberry"] |
+----+------+----------------------------------+

How can I calculate the total number of occurrences for each fruit? For example:

+------------+-------+
| fruit      | count |    
+------------+-------+
| apple      | 3     |
| orange     | 1     |
| mango      | 2     | 
| strawberry | 1     |
+------------+-------+

Some research shows that the JSON_LENGTH function can be used but I cannot find an example similar to my scenario.

Answer 1

You can use JSON_EXTRACT() function to extract each value ("apple", "mango", "strawberry" and "orange") of all three components of the arrays, and then then apply UNION ALL to combine all such queries:

SELECT comp, count(*)
FROM
(
 SELECT JSON_EXTRACT(fruit, '$[0]') as comp FROM person UNION ALL
 SELECT JSON_EXTRACT(fruit, '$[1]') as comp FROM person UNION ALL
 SELECT JSON_EXTRACT(fruit, '$[2]') as comp FROM person 
) q
WHERE comp is not null
GROUP BY comp

Indeed If your DB's version is 8, then you can also use JSON_TABLE() function :

SELECT j.fruit, count(*)
  FROM person p
  JOIN JSON_TABLE(
                 p.fruits,
                '$[*]' columns (fruit varchar(50) path '$')
       ) j
GROUP BY j.fruit;

Demo