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 (fruit, flavors)
VALUES ('apple', ARRAY [ 'sweet','sour', 'delicious']),
       ('apple', ARRAY [ 'sweet','tasty' ]),
       ('banana', ARRAY [ 'sweet', 'delicious']);

CREATE TABLE



INSERT 0 3


SELECT
    * 
FROM (
    SELECT
        *, row_number() OVER () as id
    FROM example
) s,
unnest(flavors) AS flavor


id
fruit
flavors
id
flavor


null
apple
{sweet,sour,delicious}
1
sweet

null
apple
{sweet,sour,delicious}
1
sour

null
apple
{sweet,sour,delicious}
1
delicious

null
apple
{sweet,tasty}
2
sweet

null
apple
{sweet,tasty}
2
tasty

null
banana
{sweet,delicious}
3
sweet

null
banana
{sweet,delicious}
3
delicious



SELECT 7


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}
0

banana
{delicious,sweet}
0



SELECT 2

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