c mongodb driver count and average on lookup field-CodePudding

iI have two collections, one for items, and second for ratings for that items. I want to add two values(when getting data from item collection) average of ratings and count of ratings.

public class ShopItem { //item structure
        public Guid Id {get; set;} 

        public string Name { get; set;} = string.Empty;

        public decimal Price { get; set;}

        public string Description { get; set;} = string.Empty;

        public SeasonEnum Season {get; set;}

        public DateTimeOffset CreatedDate { get; set;}
    } 

public class RatingModel {
        public Guid Id {get; set;}

        public Guid UserId {get; set;}

        public Guid ShopItemId {get; set;}

        [Range(1,5)] 
        public Int16 Rate {get; set;}

        public DateTimeOffset CreatedDate { get; set;}
    }

So i figured out that to acomplish that, first i should lookup on ratings collection, then group to add average and count, and then use projection to remove "looked-up" field. Hovever after lookup stage, value becomes bsonDocument and i am not sure how to handle it.``

 FilterDefinition<ShopItem>? filter = filterBuilder.Empty;

 if(filterOptions.NameToMatch!=null){ 
    var nameFilter = filterBuilder.Where(item=>item.Name.Contains(filterOptions.NameToMatch));
                filter &= nameFilter;
}
 if(filterOptions.SeasonToMatch!=null){ 
          var seasonFilter = filterBuilder.Where(item=>item.Season==filterOptions.SeasonToMatch);
                filter &= seasonFilter;
 }
        
 var items = await itemsCollection.
                Aggregate().
                Match(filter).
                Lookup("Rate", "Id", "ShopItemId", "Ratings"). // BsonDocument After that line
                Group(x => x.Ratings,  // 
                    g => new { 
                        AmountOfRatings = g.Count(), 
                        AverageRating = g.Average(x => x.Ratings.Rate) // not exactly sure what syntax there
                    }
                ).
                Projection(Builders<ShopItem>.Projection.Exclude("Ratings")).
                ToListAsync();

CodePudding user response：

In my opinion, you don't need the $group stage. When performing $lookup, the Rate collection is joined with ShopItem collection by ShopItemId. Hence, technically the rating is grouped by ShopItemId.

ProjectionDefinition<BsonDocument> projection = new BsonDocument
{
    {
        "AmountOfRatings",
        new BsonDocument("$size", "$Ratings")
    },
    {
        "AverageRating",
        new BsonDocument("$avg", "$Ratings.Rate")
    }
};

var items = await itemsCollection
    .Aggregate()
    .Match(filter)
    .Lookup("Rate", "_id", "ShopItemId", "Ratings")
    .Project(projection)
    .ToListAsync();