MongoDB聚合阶段过滤内嵌文档的C#驱动实现问题

我是MongoDB新手，在使用C#驱动执行聚合操作时，面临连接集合并过滤子文档的难题。现有三个集合：SalesRepresentatives、SalesBranches、SalesHistory，我需要获取指定销售代表、其所属分支，以及符合日期筛选条件的最新销售记录。

集合结构 #

SalesRepresentatives：

{ 
   _id: ObjectId('1234'), 
   FirstName: 'John', 
   LastName: 'Doe', 
   BranchId: ObjectId('1111')
} 

SalesBranches：

{
   _id: ObjectId('1111'), 
   BranchName: 'Branch 1'
}, 
{ 
   _id: ObjectId('1112'), 
   BranchName: 'Branch 2'
}

SalesHistory：

{
   _id: ObjectId('5551'),
   SalesRepresentativeId: ObjectId('1234'),
   SaleDate: '2023-07-26T11:49:46.205+00:00', 
   Amount: 10
},
{
   _id: ObjectId('5552'), 
   SalesRepresentativeId: ObjectId('1234'),
   SaleDate: '2023-07-25T11:49:46.205+00:00', 
   Amount: 11
},
{
   _id: ObjectId('5553'), 
   SalesRepresentativeId: ObjectId('1234'),
   SaleDate: '2023-07-20T11:49:46.205+00:00', 
   Amount: 15
}

期望输出 #

{ 
   _id: ObjectId('1234'), 
   FirstName: 'John', 
   LastName: 'Doe', 
   SalesBranch: { 
      _id: ObjectId('1111'), 
      BranchName: 'Branch 1'
   }, 
   Sales: [
      { _id: ObjectId('5551'), SaleDate: '2023-07-26T11:49:46.205+00:00', Amount: 10 }, 
      { _id: ObjectId('5552'), SaleDate: '2023-07-25T11:49:46.205+00:00', Amount: 11 }
   ]
}

初始查询代码 #

DateTime startDate = new DateTime(2023, 07, 25, 0, 0, 0, DateTimeKind.Utc).Date;

var matchFilter = Builders<SalesRepresentative>.Filter.Where(f => f.ID == ObjectId('1234'));
var projFilter = Builders<SalesRepresentative>.Projection.Include(p => p.ID).Include(p => p.FirstName).Include(p => p.LastName).Include(p => p.BranchId);

var pResults = await _mongoDb.SalesRepresentatives.Aggregate() 
   .Match(matchFilter) 
   .Project(projFilter) 
   .Lookup("SalesBranches", "_id", "BranchId", "SalesBranch")
   .Lookup("SalesHistory", "_id", "SalesRepresentativeId", "RecentSales") 
   .Project(new BsonDocument {
      { "_id", 1 }, 
      { "FirstName", 1 }, 
      { "LastName", 1 }, 
      { "SalesBranch._id", 1 }, 
      { "SalesBranch.BranchName", 1 }, 
      { "RecentSales._id", 1 }, 
      { "RecentSales.SaleDate", 1 }
   }).ToListAsync();

该查询可执行，但会返回该销售代表的所有销售记录，我仅需SaleDate >= startDate的记录。

修改后报错的查询代码 #

var pResults = await _mongoDb.SalesRepresentatives.Aggregate() 
   .Match(matchFilter) 
   .Project(projFilter) 
   .Lookup("SalesBranches", "_id", "BranchId", "SalesBranch")
   .Lookup("SalesHistory", "_id", "SalesRepresentativeId", "RecentSales") 
   .Project(new BsonDocument {
      { "_id", 1 }, 
      { "FirstName", 1 }, 
      { "LastName", 1 }, 
      { "SalesBranch._id", 1 }, 
      { "SalesBranch.BranchName", 1 }, 
      { "Sales", new BsonDocument { 
            { "$filter", new BsonDocument { 
                  { "input", "$RecentSales" }, 
                  { "as", "Sales" }, 
                  { "cond", new BsonDocument {
                        { "$gte", new BsonDocument { 
                              { "$$Sales.SaleDate", startDate }
                           } 
                        }
                     }
                  }
               } 
            }
         }
      }
   }).ToListAsync();

执行后出现异常：

Command aggregate failed: Invalid $project :: caused by :: 'Sales' starts with an invalid character for a user variable name.

解决方案 #

问题源于$filter的as参数：不能以大写开头，且不能与上层字段名Sales重名，需改为sale；同时需修正日期比较的构造方式。最终可用代码如下：

.Project(new BsonDocument {
   { "_id", 1 }, 
   { "FirstName", 1 }, 
   { "LastName", 1 }, 
   { "SalesBranch._id", 1 }, 
   { "SalesBranch.BranchName", 1 }, 
   { "Sales", new BsonDocument { 
         { "$filter", new BsonDocument { 
               { "input", "$RecentSales" }, 
               { "as", "sale" }, 
               { "cond", new BsonDocument {
                     { "$gte", new BsonArray {
                        startDate, new BsonDocument("$toDate", "$sale.SaleDate")
                        } 
                     }
                  }
               } 
            }
         }
      }
   }).ToListAsync();

内容的提问来源于stack exchange，提问作者Juan