sqlaggregate-functionsprestotrino

Need to format the data in trino sql


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


Solution

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