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

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 ) qWHERE comp is not nullGROUP 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 '$')       ) jGROUP BY j.fruit;

Demo

You can't do it without first creating a table with one row per fruit.

CREATE TABLE allfruits (fruit VARCHAR(10) PRIMARY KEY);INSERT INTO allfruits VALUES ('apple'), ('orange'), ('mango'), ('strawberry');

There is not a good way to generate this from the JSON.

Once you have that table, you can join it to the JSON and then use GROUP BY to count the occurrences.

SELECT fruit, COUNT(*) AS countFROM allfruitsJOIN person ON JSON_SEARCH(person.fruits, 'one', fruit) IS NOT NULLGROUP BY fruit;

Output:

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

Note that it will do a table-scan on the person table to find each fruit. This is pretty inefficient, and as your person table gets larger, it will become a performance problem.

If you want to optimize for this type of query, then you shouldn't use JSON to store an array of fruits. You should store data in a normalized way, representing the many-to-many relationship between persons and fruits with another table.

This is related to my answer to Is storing a delimited list in a database column really that bad?

I think the simplest solution would be to use JSON_TABLE function.

The query you need is

select ft.fruit, count(ft.fruit) from person,json_table(  fruits,  '$[*]' columns(     fruit varchar(128) path '$'    )   ) as ft  group by ft.fruit  ;

You can find working example in this dbfiddleFruit demo