Postgres 11

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

CREATE TABLE example(id int, fruit text, flavors text ARRAY);
INSERT INTO example (id, fruit, flavors)
VALUES (1, 'apple', ARRAY [ 'sweet','sour', 'delicious']),
       (2, 'apple', ARRAY [ 'sweet','tasty' ]),
       (3, 'banana', ARRAY [ 'sweet', 'delicious']);
3 rows affected

SELECT
    *
FROM
    example,
    unnest(flavors) AS flavor


id
fruit
flavors
flavor


1
apple
{sweet,sour,delicious}
sweet

1
apple
{sweet,sour,delicious}
sour

1
apple
{sweet,sour,delicious}
delicious

2
apple
{sweet,tasty}
sweet

2
apple
{sweet,tasty}
tasty

3
banana
{sweet,delicious}
sweet

3
banana
{sweet,delicious}
delicious


SELECT
    fruit,
    array_agg(DISTINCT flavor),
    COUNT(DISTINCT id)
FROM
    example,
    unnest(flavors) AS flavor
GROUP BY fruit


fruit
array_agg
count


apple
{delicious,sour,sweet,tasty}
2

banana
{delicious,sweet}
1

id	fruit	flavors	flavor
1	apple	{sweet,sour,delicious}	sweet
1	apple	{sweet,sour,delicious}	sour
1	apple	{sweet,sour,delicious}	delicious
2	apple	{sweet,tasty}	sweet
2	apple	{sweet,tasty}	tasty
3	banana	{sweet,delicious}	sweet
3	banana	{sweet,delicious}	delicious