MongoDB: Populate reference in $group when aggregating?

user_598683 0
Viewed 340

I have a collection that I need to group by year. My aggregation pipeline is as such:

const WorkHistoryByYear = await this.workHistroyModel.aggregate([
  {
    $group: {
      _id: '$year',
      history: {
        $push: '$$ROOT'
      }
    }
  }
]);

Which works as expected, returning:

[{
  "_id": 2003,
  "history": [
    {
      "_id": "600331b3d84ac418877a0e5a",
      "tasksPerformed": [
        "5fffb180a477c4f78ad67331",
        "5fffb18aa477c4f78ad67332"
      ],
      "year": 2003
    },
    {
      "_id": "600331dcd84ac418877a0e5c",
      "tasksPerformed": [
        "5fffb180a477c4f78ad67331"
      ],
      "year": 2003
    }
  ]
}]

but I'd like to populate a field if possible.

The WorkHistory schema has a field, tasksPerformed which is an array of ObjectId references. Here is the Task schema:

export const TaskSchema = new Schema({
  active: {
    type: Schema.Types.Boolean,
    default: true,
  },
  title: {
    type: Schema.Types.String,
    required: true,
  },
  order: {
    type: Schema.Types.Number,
    index: true,
  }
});

Is it possible to populate the referenced models within the aggregation? $lookup seems to be what I need, but I have yet to get that to work when following the documentation.

I don't do a lot of database work, so I'm having some difficulty finding the right operator(s) to use, and I've seen similar questions, but not a definitive answer.

Edit:

After adding the code from @varman's answer, my return is now:

{
  "_id": 2003,
  "history": {
    "_id": "600331b3d84ac418877a0e5a",
    "tasksPerformed": [
      "5fffb180a477c4f78ad67331",
      "5fffb18aa477c4f78ad67332"
    ],
    "year": 2003,
    "history": {
      "tasksPerformed": []
    }
  }
}

I converted the ObjectId references to strings in an effort to help the matching, but I'm still coming up short.

mongoose mongodb
2 Answers

You can do the lookup to join both collections

  • $unwind to deconstruct the array. (Array to Objects)
  • $lookup to join collections
  • $group to reconstruct the deconstructed array again

The script for the above result is

db.workHistory.aggregate([
  {
    "$unwind": "$history"
  },
  {
    "$lookup": {
      "from": "taskSchema",
      "localField": "history.tasksPerformed",
      "foreignField": "_id",
      "as": "history.tasksPerformed"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "history": {
        "$push": "$history"
      }
    }
  }
])

Working Mongo playground

But before grouping, you have collection look like this

db={
  "workHistory": [
    {
      "_id": 2003,
      "history": [
        {
          "_id": "600331b3d84ac418877a0e5a",
          "tasksPerformed": [
            "5fffb180a477c4f78ad67331",
            "5fffb18aa477c4f78ad67332"
          ],
          "year": 2003
        },
        {
          "_id": "600331dcd84ac418877a0e5c",
          "tasksPerformed": [
            "5fffb180a477c4f78ad67331"
          ],
          "year": 2003
        }
      ]
    }
  ],
  "taskSchema": [
    {
      "_id": "5fffb180a477c4f78ad67331",
      "active": true,
      "title": "first"
    },
    {
      "_id": "5fffb18aa477c4f78ad67332",
      "active": true,
      "title": "second"
    }
  ]
}

Since $unwind is expensive, we could have done aggregation

db.workHistory.aggregate([
  {
    "$lookup": {
      "from": "taskSchema",
      "localField": "tasksPerformed",
      "foreignField": "_id",
      "as": "tasksPerformed"
    }
  },
  {
    $group: {
      _id: "$year",
      history: {
        $push: "$$ROOT"
      }
    }
  }
])

Working Mongo playground

user_79757710

you could also do it without an unwind:

db.WorkHistory.aggregate(
[
    {
        $lookup: {
            from: "Tasks",
            localField: "tasksPerformed",
            foreignField: "_id",
            as: "tasksPerformed"
        }
    },
    {
        $group: {
            _id: "$year",
            history: { $push: "$$ROOT" }
        }
    }
])
user_43684850
Related