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