MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE person ( id INT, name VARCHAR(50), fruits JSON );
INSERT INTO person VALUES(1,'Tom','["apple", "orange"]'),
                         (2,'John','["apple", "mango"]'),              
                         (3,'Tony','["apple", "mango", "strawberry"]');
                         

Records: 3  Duplicates: 0  Warnings: 0


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


fruit
count(*)


226170706c6522
3

226f72616e676522
1

226d616e676f22
2

227374726177626572727922
1


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


fruit
count(*)


apple
3

orange
1

mango
2

strawberry
1

fruit	count(*)
226170706c6522	3
226f72616e676522	1
226d616e676f22	2
227374726177626572727922	1