📦Group

Group documents

The group stage groups documents by a specified _id expression and outputs a document for each group. One way to use the Match stage in a pipeline is to use the Aggregate method in a IMongoCollection<T> and chain the Group method.

IMongoCollection<T>.Aggregate()
    .Group(doc => doc.<field>, <output>)

The sample groups User documents by their profession field and outputs the total documents per group.

Group.cs

var collection = database.GetCollection<User>(collectionName);

var singleFieldAggregate = collection.Aggregate()
    .Group(u => u.Profession,
        ac => new { 
            profession = ac.Key, 
            total = ac.Sum(u => 1)
        });

var groupedProfessions = 
    await singleFieldAggregate.ToListAsync();
    
foreach (var group in groupedProfessions)
{
    Utils.Log($"{group.profession}: {group.total}");
}

db.users.aggregate([
   {
      "$group":{
         "_id":"$profession",
         "total":{
            "$sum":1
         }
      }
   }
])

----------------------------

// sample results

/* 1 */
{
	"_id" : "Lawyer",
	"total" : 44
},

/* 2 */
{
	"_id" : "Social-Media Manager",
	"total" : 35
},

/* 3 */
{
	"_id" : "Financial Adviser",
	"total" : 34
}

public class User
{
    [BsonId]
    [BsonIgnoreIfDefault] // required for replace documents 
    public ObjectId Id { get; set; }
    public Gender Gender { get; set; }
    public string FirstName {get; set; }
    public string LastName {get; set; }
    public string UserName {get; set; }
    public string Avatar {get; set; }
    public string Email {get; set; }
    public DateTime DateOfBirth {get; set; }
    public AddressCard Address {get; set; }
    public string Phone {get; set; }
    
    [BsonIgnoreIfDefault]
    public string Website {get; set; }
    public CompanyCard Company {get; set; }
    public decimal Salary { get; set; }
    public int MonthlyExpenses { get; set; }
    public List<string> FavoriteSports { get; set; }
    public string Profession { get; set; }
}

Group by an embedded document's field

You can group by any document's field you want. The sample groups the User documents by the Address.State field to find the total documents per state sorted by total.

Group.cs

var collection = database.GetCollection<User>(collectionName);

var embeddedDocFieldAggregate = collection.Aggregate()
    .Group(u => u.Address.State, // embedded document
        ac => new {state = ac.Key, total = ac.Sum(u => 1)})
    .SortBy(group => group.total); // ASC

var groupedPerCountry = await embeddedDocFieldAggregate
    .ToListAsync();

db.users.aggregate([
    { "$group" : 
    	{ 	"_id" : "$address.state", 
    			"total" : { "$sum" : 1 } } 
    	},
    { "$sort" : { "total" : 1 } }
])

----------------------------

// sample results

/* 1 */
{
	"_id" : "Arizona",
	"total" : 11
},

/* 2 */
{
	"_id" : "Missouri",
	"total" : 12
},

/* 3 */
{
	"_id" : "Iowa",
	"total" : 12
},

/* 4 */
{
	"_id" : "Kentucky",
	"total" : 13
}

public class User
{
    [BsonId]
    [BsonIgnoreIfDefault] // required for replace documents 
    public ObjectId Id { get; set; }
    public Gender Gender { get; set; }
    public string FirstName {get; set; }
    public string LastName {get; set; }
    public string UserName {get; set; }
    public string Avatar {get; set; }
    public string Email {get; set; }
    public DateTime DateOfBirth {get; set; }
    public AddressCard Address {get; set; }
    public string Phone {get; set; }
    
    [BsonIgnoreIfDefault]
    public string Website {get; set; }
    public CompanyCard Company {get; set; }
    public decimal Salary { get; set; }
    public int MonthlyExpenses { get; set; }
    public List<string> FavoriteSports { get; set; }
    public string Profession { get; set; }
}

Match - Group - Project

The sample combines 4 stages to calculate the average monthly expenses per gender.

The $match stage filters documents based on the salary
The $group stage groups the filtered documents per gender and calculate the average monthly expenses
The $project stage formats the gender as a string
The last stage $sort, simply sorts the results per average monthly expenses

var collection = database.GetCollection<User>(collectionName);

var excercice1Aggregate = collection.Aggregate()
    .Match(Builders<User>.Filter.Gte(u => u.Salary, 1500) &
           Builders<User>.Filter.Lte(u => u.Salary, 3000))
    .Group(u => u.Gender,
        ac => new
        {
            gender = ac.Key,
            averageMonthlyExpenses = ac.Average(u => u.MonthlyExpenses),
            total = ac.Sum(u => 1)
        })
    .Project(group => new 
    {
        Gender = group.gender == 0 ? "Male" : "Female",
        AverageMonthlyExpenses = group.averageMonthlyExpenses,
        Total = group.total
    })
    .SortByDescending(group => group.AverageMonthlyExpenses);
    
var excercice1Result = await excercice1Aggregate.ToListAsync();

var collection = database.GetCollection<User>(collectionName);

var linqQuery = collection.AsQueryable()
    .Where(u => u.Salary > 1500 && u.Salary < 3000)
    .GroupBy(u => u.Gender)
    .Select(ac => new
    {
        gender = ac.Key,
        averageMonthlyExpenses = 
            Math.Ceiling(ac.Average(u => u.MonthlyExpenses)),
        total = ac.Sum(u => 1)
    })
    .OrderBy(group => group.total);

var excercice1LinqResult = linqQuery.ToList();

db.users.aggregate([
   {
      "$match":{
         "salary":{
            "$gt":NumberDecimal("1500"),
            "$lt":NumberDecimal("3000")
         }
      }
   },
   {
      "$group":{
         "_id":"$gender",
         "__agg0":{
            "$avg":"$monthlyExpenses"
         },
         "__agg1":{
            "$sum":1
         }
      }
   },
   {
      "$project":{
         "gender":"$_id",
         "averageMonthlyExpenses":{
            "$ceil":"$__agg0"
         },
         "total":"$__agg1",
         "_id":0
      }
   },
   {
      "$sort":{
         "total":1
      }
   }
])

-----------------------------------------

// results with ceil
/* 1 */
{
	"gender" : 0,
	"averageMonthlyExpenses" : 4127,
	"total" : 181
},

/* 2 */
{
	"gender" : 1,
	"averageMonthlyExpenses" : 4037,
	"total" : 188
}


// results without ceil

/* 1 */
{
	"gender" : 0,
	"averageMonthlyExpenses" : 4126.922651933702,
	"total" : 181
},

/* 2 */
{
	"gender" : 1,
	"averageMonthlyExpenses" : 4036.478723404255,
	"total" : 188
}

The LINQ query supports the Math.Ceiling method and can produce an average of Int32 type.

PreviousMatch NextUnwind

Last updated 5 years ago

hashtagGroup documents

hashtagGroup by an embedded document's field

hashtagMatch - Group - Project

Group documents

Group by an embedded document's field

Match - Group - Project