title: How to calculate count of each value in MySQL JSON array?
body: <p>I have a <code>MySQL</code> table with the following definition:</p>
  
  <pre><code>mysql&gt; desc person;
  +--------+---------+------+-----+---------+-------+
  | Field  | Type    | Null | Key | Default | Extra |
  +--------+---------+------+-----+---------+-------+
  | id     | int(11) | NO   | PRI | NULL    |       |
  | name   | text    | YES  |     | NULL    |       |
  | fruits | json    | YES  |     | NULL    |       |
  +--------+---------+------+-----+---------+-------+
  </code></pre>
  
  <p>The table has some sample data as follows:</p>
  
  <pre><code>mysql&gt; select * from person;
  +----+------+----------------------------------+
  | id | name | fruits                           |
  +----+------+----------------------------------+
  |  1 | Tom  | ["apple", "orange"]              |
  |  2 | John | ["apple", "mango"]               |
  |  3 | Tony | ["apple", "mango", "strawberry"] |
  +----+------+----------------------------------+
  </code></pre>
  
  <p>How can I calculate the total number of occurrences for each fruit? For example:</p>
  
  <pre><code>+------------+-------+
  | fruit      | count |    
  +------------+-------+
  | apple      | 3     |
  | orange     | 1     |
  | mango      | 2     | 
  | strawberry | 1     |
  +------------+-------+
  </code></pre>
  
  <p>Some research shows that the <code>JSON_LENGTH</code> function can be used but I cannot find an example similar to my scenario. </p>