Need to format the data in trino sql

Question

I have the table FruitMarket

Fruit	Color	City
apple	red	Shimala
apple	Green	Shimal
apple	blue	Shimala
apple	yellow	Manali
grape	black	Nagpur
grape	orange	Nashik
grape	purple	Nashik
grape	white	Nagpur

Need to display the data using trino SQL in below format: So the output table is in below format-

Fruit   fruitInfo
apple   Shimala={red,green,blue},Manali={yellow}
grape   Nagpur={black,white},Nashik={orange,purple}

I want the fruitInfo in the form of map

I tried this:

select nettingset, array_distinct(flatten(array_agg(city)))from FruitMarket;

not array_agg function is not working. I know the functions map_from_entries and element_at but dont know how to use it here

Answer 1

multimap_agg looks like a function you need:

-- sample data
WITH dataset(Fruit, Color, City) AS (
   values ('apple', 'red',  'Shimala'),
    ('apple',   'Green',    'Shimala'),
    ('apple',   'blue', 'Shimala'),
    ('apple',   'yellow',   'Manali'),
    ('grape',   'black',    'Nagpur'),
    ('grape',   'orange',   'Nashik'),
    ('grape',   'purple',   'Nashik'),
    ('grape',   'white',    'Nagpur')
)

-- query
select Fruit, multimap_agg(City, Color) fruitInfo
from dataset
group by Fruit;

Output:

Fruit	fruitInfo
apple	{Shimala=[red, Green, blue], Manali=[yellow]}
grape	{Nagpur=[black, white], Nashik=[orange, purple]}