Sort MongoDB result on aggregation of list element field

Question

I just started to play around with MongoDB on C#. I use the Restaurant sample data (https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json).

What I am trying to figure out is, how I can sort the restaurants by the total of their rating score. Can someone provide a sample on how to do that using the AggregateFluent API? I got lost with that.

Thanks!

Answer 1

I would create DTO classes for your collection:

public class Restaurant
{
    public ObjectId _id { get; set; }
    public address address { get; set; }
    public string borough { get; set; }
    public string cuisine { get; set; }
    public grades[] grades {get;set;}
    public string name { get; set; }
    public string restaurant_id {get;set;}
}

public class grades
{
    public DateTime date {get;set;}
    public string grade {get;set;}
    public int? score {get;set;}
}

public class address
{
    public string building { get; set; }
    public double[] coord { get; set; }
    public string street { get; set; }
    public string zipcode { get; set;}
}

And if you create your collection as :

var collection = db.GetCollection<Restaurant>("restaurants");

you could just order your result this way:

collection
    .Aggregate()
    .Project(r => new {Restarant = r.name, TotalScore= r.grades.Sum(g=>g.score)})
    .SortByDescending(x=>x.TotalScore)
    .ToList()