mongodb (aggregation) - $lookup with the lookup result

Question

i am new in aggregation framework and have the following problem/question.

My collections looks like this:

users

    _id: ObjectId('604caef28aa89e769585f0c4')
    baseData: { 
       firstName: "max"
       username: "max_muster"
       ...
       ...
    }

activitiesFeed

    _id: ObjectId('604cb97c99bbd77b54047370')
    userID: "604caef28aa89e769585f0c4"     // the user id 
    activity: "604caf718aa89e769585f0c8"   // the activity id 
    viewed: false 
    

activities

  _id: ObjectId('604caf718aa89e769585f0c8')
 "baseData": {
      "userID":"604caef28aa89e769585f0c4",
      "type":0,
      "title":"Alessandro send you a friend request.",
      "description":"",
      "actionID":"604caef28aa89e769585f0c4"
   },

aggregate function

const response = ActivityFeed.aggregate([
      {
        $match: { userID: ObjectId(args.user) },
      },
      {
        $lookup: {
          from: 'activities',
          localField: 'activity',
          foreignField: '_id',
          as: 'lookup_activities',
        },
      },
      { $unwind: '$activities' },
      {
        $lookup: {
          from: 'users',
          localField: 'lookup_activities.baseData.actionID',
          foreignField: '_id',
          as: 'lookup_users',
        },
      },
    ]);

How i can made a lookup with the first lookup result?

I made a lookup to the activities collection. This collection holds the id for the second lookup baseData.userID. But with this approach i have no result with the second lookup.

The reason why i made a join to the activities collection is that the actionID can hold a userID or a document id from another collection. It depend on the type in the activities collection.

Is it in the aggregation framework possible to made a lookup which depends on the type and of the previous lookup?

Thank you in advance.

Answer 1

All I did is fixing your aggregation. You unwind the wrong field and please make sure all your ID fields are ObjectId. I highly recommend using MongoDB Compass to assist your aggregation if you are new to the aggregation function. They have stage by stage assistance GUI which will really make your life easier.

mongoplayground

db.activitiesFeed.aggregate([
  {
    $match: {
      userID: ObjectId("604caef28aa89e769585f0c4")
    },
  },
  {
    $lookup: {
      from: "activities",
      localField: "activity",
      foreignField: "_id",
      as: "lookup_activities",
    },
  },
  {
    $unwind: "$lookup_activities"
  },
  {
    $lookup: {
      from: "users",
      localField: "lookup_activities.baseData.actionID",
      foreignField: "_id",
      as: "lookup_users",
    },
  },
])

Sample Used

 db={
  "users": [
    {
      _id: ObjectId("604caef28aa89e769585f0c4"),
      baseData: {
        firstName: "max",
        username: "max_muster"
      }
    }
  ],
  "activitiesFeed": [
    {
      _id: ObjectId("604cb97c99bbd77b54047370"),
      userID: ObjectId("604caef28aa89e769585f0c4"),
      activity: ObjectId("604caf718aa89e769585f0c8"),
      viewed: false
    }
  ],
  "activities": [
    {
      _id: ObjectId("604caf718aa89e769585f0c8"),
      "baseData": {
        "userID": ObjectId("604caef28aa89e769585f0c4"),
        "type": 0,
        "title": "Alessandro send you a friend request.",
        "description": "",
        "actionID": ObjectId("604caef28aa89e769585f0c4")
      },
    }
  ]
}