MongoDB Mongoose 按日期范围查询深层嵌套的子文档数组

我有一个问题是与另一个问题类似 https://stackoverflow.com/questions/40971909/mongodb-mongoose-querying-an-array-of-objects-by-date但不完全相同，因为我的数据结构嵌套得更深，并且接受的答案没有解决问题。

技术：MongoDB 3.6、Mongoose 5.5、NodeJS 12

我正在尝试查询深层嵌套的对象数组。该查询将接受用户的“开始日期”和“结束日期”。项目报告是一个子文档数组，其中包含另一个子文档“工作完成者”数组。所有在开始和结束日期范围内具有“CompletedDate”的 WorkDoneBy 对象都应与其他几个属性一起返回。

期望的返回属性：

RecordID、RecordType、状态、ItemReport.WorkDoneBy.DateCompleted、ItemReport.WorkDoneBy.CompletedHours、ItemReport.WorkDoneBy.Person

记录架构：

let RecordsSchema = new Schema({
  RecordID: {
    type: Number,
    index: true
  },
  RecordType: {
    type: String,
    enum: ['Item', 'OSW']
  },
  Status: {
    type: String
  },
  // ItemReport array of subdocuments
  ItemReport: [ItemReportSchema],
}, {
  collection: 'records',
  selectPopulatedPaths: false
});

let ItemReportSchema = new Schema({
  // ObjectId reference
  ReportBy: {
    type: Schema.Types.ObjectId,
    ref: 'people'
  },
  ReportDate: {
    type: Date,
    required: true
  },
  WorkDoneBy: [{
    Person: {
      type: Schema.Types.ObjectId,
      ref: 'people'
    },
    CompletedHours: {
      type: Number,
      required: true
    },
    DateCompleted: {
      type: Date
    }
  }],
});

尝试1：

db.records.aggregate([
    {
        "$match": {
            "ItemReport.WorkDoneBy.DateCompleted": { "$gt": new Date("2017-01-01T12:00:00.000Z"), "$lt": new Date("2018-12-31T12:00:00.000Z") }
        }
    },
    {
        "$project": {
            "ItemReport.WorkDoneBy": {
                "$filter": {
                    "input": "$ItemReport.WorkDoneBy",
                    "as": "value",
                    "cond": {
                        "$and": [
                            { "$ne": [ "$$value.DateCompleted", null ] },
                            { "$gt": [ "$$value.DateCompleted", new Date("2017-01-01T12:00:00.000Z") ] },
                            { "$lt": [ "$$value.DateCompleted", new Date("2018-12-31T12:00:00.000Z") ] }
                        ]
                    }
                }
            }
        }
    }
])

尝试 1 返回：

{ "_id" : ObjectId("5dcb6406e63830b7aa54269d"), "ItemReport" : [ { "WorkDoneBy" : [ ] } ] }
{ "_id" : ObjectId("5dcb6406e63830b7aa5426fb"), "ItemReport" : [ { "WorkDoneBy" : [ ] } ] }
{ "_id" : ObjectId("5dcb6406e63830b7aa542708"), "ItemReport" : [ { "WorkDoneBy" : [ ] } ] }
{ "_id" : ObjectId("5dcb6406e63830b7aa542712"), "ItemReport" : [ { "WorkDoneBy" : [ ] } ] }

期望的回报（为简洁起见删除了 _id）：

请注意，仅当 WorkDoneBy 数组中的对象位于指定日期范围内时，才应返回它们。例如，RecordID 9018 ItemReport.WorkDoneBy 实际上具有 2016 年的日期，但不会返回这些日期，因为它们不在指定的日期范围内。

{ "ItemReport" : [ { "WorkDoneBy" : [ { "CompletedHours" : 11, "DateCompleted" : ISODate("2017-09-29T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") }, { "CompletedHours" : 36, "DateCompleted" : ISODate("2018-05-18T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") }, { "CompletedHours" : 32, "DateCompleted" : ISODate("2018-05-18T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") } ] } ], "RecordID" : 9018, "RecordType" : "Item", "Status" : "Done" }
{ "ItemReport" : [ { "WorkDoneBy" : [ { "CompletedHours" : 1.5, "DateCompleted" : ISODate("2017-09-01T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fe5f") } ] } ], "RecordID" : 9019, "RecordType" : "Item", "Status" : "Done" }
{ "ItemReport" : [ { "WorkDoneBy" : [ { "CompletedHours" : 2, "DateCompleted" : ISODate("2017-09-08T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") }, { "CompletedHours" : 18, "DateCompleted" : ISODate("2017-09-15T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") }, { "CompletedHours" : 7, "DateCompleted" : ISODate("2017-09-20T04:00:00Z"), "Person" : ObjectId("5dcb6409e63830b7aa54fd6e") } ] } ], "RecordID" : 9017, "RecordType" : "Item", "Status" : "Done" }

这里的问题是WorkDoneBy是一个嵌套在另一个数组中的数组 (ItemReport）。因此单身$filter还不够，因为您需要迭代两次。你可以加$map https://docs.mongodb.com/manual/reference/operator/aggregation/map/index.html迭代外部数组：

db.records.aggregate([
    {
        "$project": {
            "ItemReport": {
                $map: {
                    input: "$ItemReport",
                    as: "ir",
                    in: {
                        WorkDoneBy: {
                            $filter: {
                                input: "$$ir.WorkDoneBy",
                                as: "value",
                                cond: {
                                    "$and": [
                                        { "$ne": [ "$$value.DateCompleted", null ] },
                                        { "$gt": [ "$$value.DateCompleted", new Date("2017-01-01T12:00:00.000Z") ] },
                                        { "$lt": [ "$$value.DateCompleted", new Date("2018-12-31T12:00:00.000Z") ] }
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    }
])