Mongodb aggregation lookup to add field in each array with condition

Question

I have 3 collections.

User:

{
   "_id":ObjectId("60a495cdd4ba8b122899d415"),
   "email":"br9@gmail.com",
   "username":"borhan"
}

Panel:

{
   "_id": ObjectId("60a495cdd4ba8b122899d417"),
   "name": "borhan",
   "users": [
      {
          "role": "admin",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a495cdd4ba8b122899d418"),
          "user": ObjectId("60a495cdd4ba8b122899d415")
      },
      {
          "role": "member",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a49600d4ba8b122899d41a"),
          "user": ObjectId("60a34e167958972d7ce6f966")
       }
    ],
}

Team:

{
   "_id":ObjectId("60a495e0d4ba8b122899d419"),
   "title":"New Teams",
   "users":[
      ObjectId("60a495cdd4ba8b122899d415")
   ],
   "panel":ObjectId("60a495cdd4ba8b122899d417")
}

I want to receive a output from querying Panel colllection just like this:

{
   "_id": ObjectId("60a495cdd4ba8b122899d417"),
   "name": "borhan",
   "users": [
      {
          "role": "admin",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a495cdd4ba8b122899d418"),
          "user": ObjectId("60a495cdd4ba8b122899d415"),
          "teams":[
             {
                 "_id":ObjectId("60a495e0d4ba8b122899d419"),
                 "title":"New Teams",
                 "users":[
                      ObjectId("60a495cdd4ba8b122899d415")
                  ],
                 "panel":ObjectId("60a495cdd4ba8b122899d417")
             }
          ]
      },
      {
          "role": "member",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a49600d4ba8b122899d41a"),
          "user": ObjectId("60a34e167958972d7ce6f966")
       }
    ],
}

I mean i want to add teams field (which is array of teams that user is existed on it) to each user in Panel collection

Here is my match query in mongoose to select specific panel:

panel_model.aggregate([
   {
      $match: {              
         users: {
            $elemMatch: {user: ObjectId("60a495cdd4ba8b122899d415"), role:"admin"}
         }
      }
   },
])

Is it possible to get my output with $lookup or $addFields aggregations?

Answer 1

You need to join all three collections,

• $unwind to deconstruct the array
• $lookup there are two kind of lookups which help to join collections. First I used Multiple-join-conditions-with--lookup, and I used standrad lookup to join Users and Teams collections.
• $match to match the user's id
• $expr - when you use $match inside lookup, u must use it.
• $set to add new fields
• $group to we already destructed using $unwind. No we need to restructure it

here is the code

db.Panel.aggregate([
  { $unwind: "$users" },
  {
    "$lookup": {
      "from": "User",
      "let": { uId: "$users.user" },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [ "$_id", "$$uId" ]
            }
          }
        },
        {
          "$lookup": {
            "from": "Team",
            "localField": "_id",
            "foreignField": "users",
            "as": "teams"
          }
        }
      ],
      "as": "users.join"
    }
  },
  {
    "$set": {
      "users.getFirstElem": {
        "$arrayElemAt": [ "$users.join", 0 ]
      }
    }
  },
  {
    $set: {
      "users.teams": "$users.getFirstElem.teams",
      "users.join": "$$REMOVE",
      "users.getFirstElem": "$$REMOVE"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "name": { "$first": "name" },
      "users": { $push: "$users" }
    }
  }
])

Working Mongo playground

Note : Hope the panel and user collections are in 1-1 relationship. Otherwise let me know