C# MongoDB Driver查询嵌套数组返回所有匹配子元素问题

问题背景 #

实体类定义如下：

public class ActionResponse
{
    public string Id { get; set; }
    public List<ActionResponseItem> ResponseItems { get; set; }
}

public class ActionResponseItem
{
    public string Id { get; set; }
    public DateTime Created { get; set; }
    public List<string> Options { get; set; }
}

测试集合数据如下：

[
    {
    "_id": ObjectId("62bd901c4af4e9d2e1287984"),
    "ResponseItems": [
      {
        "_id": ObjectId("62bd9068b4598dd0bb07b536"),
        "Created": ISODate("2022-05-30T08:45:31.662Z"),
        "Options": ["Some string 1", "Some string 2"]
      },
      {
        "_id": ObjectId("62bd906e35087e146b6b28f6"),
        "Created": ISODate("2022-05-30T08:46:56.192Z"),
        "Options": ["Some string 3", "Some string 4"]
      }
    ]
  },
  {
    "_id": ObjectId("5e29560a2c4c55d421a4e1b4"),
    "ResponseItems": [
      {
        "_id": ObjectId("62bd62ab886b3fde8368ec39"),
        "Created": ISODate("2022-06-30T08:45:31.662Z"),
        "Options": ["Some string 1", "Some string 2"]
      },
      {
        "_id": ObjectId("62bd6300886b3fde8368ec3f"),
        "Created": ISODate("2022-06-30T08:46:56.192Z"),
        "Options": ["Some string 3", "Some string 4"]
      },
      {
        "_id": ObjectId("62bd6349886b3fde8368ec46"),
        "Created": ISODate("2022-06-30T08:48:09.226Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64a88f5b6b24b6de199e"),
        "Created": ISODate("2022-06-30T08:54:00.450Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64aa8f5b6b24b6de19a7"),
        "Created": ISODate("2022-06-30T08:54:02.767Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64af8f5b6b24b6de19b1"),
        "Created": ISODate("2022-06-30T08:54:07.896Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64b38f5b6b24b6de19bc"),
        "Created": ISODate("2022-06-30T08:54:11.837Z"),
        "Options": null
      },
      {
        "_id": ObjectId("62bd64b78f5b6b24b6de19c8"),
        "Created": ISODate("2022-06-30T08:54:15.588Z"),
        "Options": ["Some string 5", "Some string 6"]
      },
      {
        "_id": ObjectId("62bd64bd8f5b6b24b6de19d5"),
        "Created": ISODate("2022-06-30T08:54:21.494Z"),
        "Options": ["Some string 7"]
      },
      {
        "_id": ObjectId("62bd654d8f5b6b24b6de331d"),
        "Created": ISODate("2022-06-30T08:56:45.487Z"),
        "Options": null
      }
    ]
  }
]

查询需求 #

筛选满足以下所有条件的数据，直接返回匹配的嵌套数组子元素列表：

• ActionResponse.Id = '5e29560a2c4c55d421a4e1b4'
• ActionResponseItem.Created >= ISODate("2022-06-30T08:54:17Z")
• ActionResponseItem.Created <= ISODate("2022-06-30T10:09:18.403Z")

注：给出的预期结果中_id=62bd64b78f5b6b24b6de19c8的项Created时间为08:54:15，早于筛选起始时间08:54:17，实际不会被返回，属于预期结果笔误，正确返回值共2条

原有方案问题说明 #

• find+$elemMatch投影：$elemMatch投影只会返回数组中第一个匹配的元素，无法返回所有符合条件的数组项
• 仅使用聚合$match阶段：只会筛选出符合条件的父文档，不会对嵌套数组做过滤、结构转换，因此会返回整个父文档

正确实现方案 #

推荐使用聚合管道$filter实现，性能优于$unwind方案，不需要拆分数组再重组。

Mongo Shell 写法 #

db.collection.aggregate([
  // 匹配目标父文档
  {
    $match: {
      "_id": ObjectId("5e29560a2c4c55d421a4e1b4")
    }
  },
  // 过滤嵌套数组，仅保留符合时间条件的元素
  {
    $project: {
      ResponseItems: {
        $filter: {
          input: "$ResponseItems",
          cond: {
            $and: [
              { $gte: ["$$this.Created", ISODate("2022-06-30T08:54:17Z")] },
              { $lte: ["$$this.Created", ISODate("2022-06-30T10:09:18.403Z")] }
            ]
          }
        }
      },
      _id: 0
    }
  },
  // 以下两个阶段用于直接返回扁平化的子元素列表，不需要可删除
  { $unwind: "$ResponseItems" },
  { $replaceRoot: { newRoot: "$ResponseItems" } }
])

C# 驱动写法 #

var targetId = new ObjectId("5e29560a2c4c55d421a4e1b4");
var from = new DateTime(2022, 6, 30, 8, 54, 17, DateTimeKind.Utc);
var to = new DateTime(2022, 6, 30, 10, 9, 18, 403, DateTimeKind.Utc);

var result = await _collection.Aggregate()
    .Match(Builders<ActionResponse>.Filter.Eq(a => a.Id, targetId.ToString()))
    .Project(p => new 
    {
        ResponseItems = p.ResponseItems.Where(i => i.Created >= from && i.Created <= to)
    })
    .Unwind(x => x.ResponseItems)
    .ReplaceRoot<ActionResponseItem>("$ResponseItems")
    .ToListAsync();

如果不需要返回扁平化的子元素列表，仅需拿到过滤后的数组，去掉最后Unwind和ReplaceRoot阶段，取结果第一个元素的ResponseItems属性即可。

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