MongoDB Mongoose aggregate query deeply nested array remove empty results and populate references

Question

This question is a follow up to a previous question for which I have accepted an answer already. I have an aggregate query that returns the results of a deeply nested array of subdocuments based on a date range. The query returns the correct results within the specified date range, however it also returns an empty array for the results that do not match the query.

Technologies: MongoDB 3.6, Mongoose 5.5, NodeJS 12

Question 1: Is there any way to remove the results that don't match the query?

Question 2: Is there any way to 'populate' the Person db reference in the results? For example to get the Person Display Name I usually use 'populate' such as find().populate({ path: 'Person', select: 'DisplayName'})

Records schema

let RecordsSchema = new Schema({
  RecordID: {
    type: Number,
    index: true
  },
  RecordType: {
    type: String
  },
  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
    }
  }],
});

Query

Works but also returns empty results and also need to populate the Display Name property of the Person db reference

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

Actual Results

{
    "_id": "5dcb6406e63830b7aa5427ca",
    "ItemReport": [
        {
            "WorkDoneBy": [
                {
                    "_id": "5dcb6406e63830b7aa53d8ea",
                    "PersonID": 111,
                    "ReportID": 8855,
                    "CompletedHours": 3,
                    "DateCompleted": "2017-01-20T05:00:00.000Z",
                    "Person": "5dcb6409e63830b7aa54fdba"
                }
            ]
        }
    ]
},
{
    "_id": "5dcb6406e63830b7aa5427f1",
    "ItemReport": [
        {
            "WorkDoneBy": [
                {
                    "_id": "5dcb6406e63830b7aa53dcdc",
                    "PersonID": 4,
                    "ReportID": 9673,
                    "CompletedHours": 17,
                    "DateCompleted": "2017-05-18T04:00:00.000Z",
                    "Person": "5dcb6409e63830b7aa54fd69"
                },
                {
                    "_id": "5dcb6406e63830b7aa53dcdd",
                    "PersonID": 320,
                    "ReportID": 9673,
                    "CompletedHours": 3,
                    "DateCompleted": "2017-05-18T04:00:00.000Z",
                    "Person": "5dcb6409e63830b7aa54fe88"
                }
            ]
        }
    ]
},
{
    "_id": "5dcb6406e63830b7aa5427f2",
    "ItemReport": [
        {
            "WorkDoneBy": []
        }
    ]
},
{
    "_id": "5dcb6406e63830b7aa5427f3",
    "ItemReport": [
        {
            "WorkDoneBy": []
        }
    ]
},
{
    "_id": "5dcb6406e63830b7aa5427f4",
    "ItemReport": [
        {
            "WorkDoneBy": []
        }
    ]
},
{
    "_id": "5dcb6406e63830b7aa5427f5",
    "ItemReport": [
        {
            "WorkDoneBy": []
        }
    ]
},

Desired results

Note the results with an empty "WorkDoneBy" array are removed (question 1), and the "Person" display name is populated (question 2).

{
    "_id": "5dcb6406e63830b7aa5427f1",
    "ItemReport": [
        {
            "WorkDoneBy": [
                {
                    "_id": "5dcb6406e63830b7aa53dcdc",
                    "CompletedHours": 17,
                    "DateCompleted": "2017-05-18T04:00:00.000Z",
                    "Person": {
                      _id: "5dcb6409e63830b7aa54fe88",
                      DisplayName: "Joe Jones"
                    }
                },
                {
                    "_id": "5dcb6406e63830b7aa53dcdd",
                    "CompletedHours": 3,
                    "DateCompleted": "2017-05-18T04:00:00.000Z",
                    "Person": {
                      _id: "5dcb6409e63830b7aa54fe88",
                      DisplayName: "Alice Smith"
                    }
                }
            ]
        }
    ]
},

mickl · Accepted Answer · 2019-12-05 02:20:27Z

1

First question is relatively easy to answer and there are multiple ways to do that. I would prefer using $anyElementTrue along with $map as those operators are pretty self-explanatory.

{
    "$match": {
        $expr: { $anyElementTrue: { $map: { input: "$ItemReport", in: { $gt: [ { $size: "$$this.WorkDoneBy" }, 0 ] } } } }
    }
}

MongoPlayground

Second part is a bit more complicated but still possible. Instead of populate you need to run $lookup to bring the data from other collection. The problem is that your Person values are deeply nested so you need to prepare a list of id values before using $reduce and $setUnion. Once you get the data you need to merge your nested objects with people entities using $map and $mergeObjects.

{
    $addFields: {
        people: {
            $reduce: {
                input: "$ItemReport",
                initialValue: [],
                in: { $setUnion: [ "$$value", "$$this.WorkDoneBy.Person" ] }
            }
        }
    }
},
{
    $lookup: {
        from: "people",
        localField: "peopleIds",
        foreignField: "_id",
        as: "people"
    }
},
{
    $project: {
        _id: 1,
        ItemReport: {
            $map: {
                input: "$ItemReport",
                as: "ir",
                in: {
                    WorkDoneBy: {
                        $map: {
                            input: "$$ir.WorkDoneBy",
                            as: "wdb",
                            in: {
                                $mergeObjects: [
                                    "$$wdb",
                                    {
                                        Person: { $arrayElemAt: [{ $filter: { input: "$people", cond: { $eq: [ "$$this._id", "$$wdb.Person" ] } } } , 0] }
                                    }
                                ]
                            }
                        }
                    }
                }
            }
        }
    }
}

Complete Solution

answered Dec 5, 2019 at 2:20

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pengz Over a year ago

Excellent thank you SO much. The only issue I am having is that I am also trying to return some other fields on the record, for example RecordID, RecordType, and Status. I tried adding those fields to the last $project statement at the bottom but it doesn't seem to return those fields.

$project: {           RecordID: 1,           RecordType: 1,           Status: 1,           ItemReport: {             $map: { ...

mickl Over a year ago

@pengz you should include them on $map level. Line ~ 9 and then ~ 93 like RecordID: "$$ir.RecordID",(line numbers from my Playground)

pengz Over a year ago

Hm, $$ir.RecordID isn't working I think perhaps because RecordID, RecordType, etc. properties are not on the Item Report level, they are on the 'top' level above.

pengz Over a year ago

Oh I think I got it! I just had to add RecordID: 1, RecordType: 1, etc to both project statements. Seriously thank you SOOO much this is not the first time you've helped me tremendously. :) e.g. "$project": { "RecordID": 1, "RecordType": 1, "ItemReport": { $map: {..

Collectives™ on Stack Overflow

MongoDB Mongoose aggregate query deeply nested array remove empty results and populate references

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related