MongoDB Mongoose按日期范围查询深度嵌套的子文档数组 [英] MongoDB Mongoose querying a deeply nested array of subdocuments by date range

问题描述

我有一个与其他问题类似的问题， ，但不完全相同，因为我的数据结构嵌套得更深，并且可接受的答案不能解决问题.

I have a question that is similar to this other question but not exactly the same because my data structure is more deeply nested, and the accepted answer did not resolve the issue.

技术:MongoDB 3.6，Mongoose 5.5，NodeJS 12

Technologies: MongoDB 3.6, Mongoose 5.5, NodeJS 12

我正在尝试查询对象的深层嵌套数组.该查询将接受来自用户的开始日期"和结束日期".项目报表是一个子文档数组，其中包含另一个子文档数组"Work Done By".在开始日期和结束日期范围内具有"CompletedDate"的所有WorkDoneBy对象都应连同其他几个属性一起返回.

I am trying to query a deeply nested array of objects. The query will accept a "Start Date" and an "End Date" from the user. Item Report is an array of subdocuments that contains another array of subdocuments "Work Done By". All WorkDoneBy objects that have a "CompletedDate" in the Start and End date range should be returned along with several other properties.

所需的返回属性:

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

RecordID, RecordType, Status, 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年的日期，但由于它们不在指定的日期范围内，因此不会返回.

Note that objects in the WorkDoneBy array should be returned ONLY if they are within the specified date range. For example RecordID 9018 ItemReport.WorkDoneBy actually has dates in 2016 but those are not returned because they are not within the specified date range.

{ "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 来遍历外部数组:

The problem here is that WorkDoneBy is an array nested in another array (ItemReport). Therefore single $filter is not enough since you need to iterate twice. You can add $map to iterate over the outer array:

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") ] }
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    }
])

这篇关于MongoDB Mongoose按日期范围查询深度嵌套的子文档数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！