如果不先创建一个每个水果行一行的表，就无法做到这一点。

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

没有一种从JSON生成此内容的好方法。

一旦有了该表，就可以将其连接到JSON，然后使用GROUP BY来计算出现的次数。

SELECT fruit,COUNT(*) AS count
FROM allfruits
JOIN person ON JSON_SEARCH(person.fruits,'one',fruit) IS NOT NULL
GROUP BY fruit;

输出：

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

请注意，它将对人员表进行表扫描以查找每个水果。这是相当低效的，并且随着您的人员表越来越大，它将成为性能问题。

如果要针对这种类型的查询进行优化，则不应使用JSON来存储一系列水果。您应该以标准化的方式存储数据，用另一个表表示人与水果之间的多对多关系。

这与我对Is storing a delimited list in a database column really that bad?的回答

您可以使用JSON_EXTRACT()函数提取数组的所有三个分量的每个值（“ apple”，“ mango”，“ strawberry”和“ orange”），然后应用UNION ALL合并所有此类查询：

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

确实，如果您的数据库版本为8，那么您还可以使用JSON_TABLE()函数：

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

Demo

我认为最简单的解决方案是使用JSON_TABLE函数。

您需要的查询是

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

您可以在此dbfiddle中找到工作示例 Fruit demo