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.
Advertisement
Answer
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;