mongodbmongoosemongodb-queryaggregation-frameworkmongodb4.0

How to do $lookup in an array's field and add the foreign table's content in the same query?


I am trying to make a query in which I have a db_task query that contains the task id and the users assigned to it. I have to fetch the user details which are present in db_user collection and add the incoming details into the same document.

db_task

{
    "_id" : ObjectId("5d8b522d0cf2579c57bc8ce0"),
    "users" : [ 
        {
            "user_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
        "is_finished" : false
        }, 
        {
            "user_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
            "is_finished" : false
        }
    ]
}

The users field the users who are assigned that task, I want to do a lookup on the db_user query which would get me the details inside the same embedded document.

db_user

{
    "_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
    "first_name" : "Harry",
    "last_name" : "Paul"
},

{
    "_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
    "first_name" : "Aaron",
    "last_name" : "Potter"
}

I tried to do $lookup on the db_user table with "users.user_id" but that would fetch me a new field and then I tried to concatenate those arrays with "$concatArrays" but the result still wasn't what I expected.

I want to get the output in a format something like this

db_task

{
    "_id" : ObjectId("5d8b522d0cf2579c57bc8ce0"),
    "users" : [ 
        {
            "user_id" : ObjectId("5d8b522d0cf2579e27bc8ce3"),
            "is_finished" : false,
            "user_info":{
                 "first_name" : "Harry",
                 "last_name" : "Paul"
              }
        }, 
        {
            "user_id" : ObjectId("5d6f6d25e079b9fb7d858236"),
            "is_finished" : false,
            "user_info":{
                  "first_name" : "Aaron",
                   "last_name" : "Potter"
              }
        }
    ]
}

Solution

  • Altough they're working, the provided solutions, with unwind and group, can be expensive in resources. Here's a better solution in only two stages :

    db.db_task.aggregate([
      {
        $lookup: {
          from: "db_user",
          localField: "users.user_id",
          foreignField: "_id",
          as: "usersInfos"
        }
      },
      {
        $project: {
          users: {
            $map: {
              input: "$usersInfos",
              as: "ui",
              in: {
                $mergeObjects: [
                  "$$ui",
                  {
                    $arrayElemAt: [
                      {
                        $filter: {
                          input: "$users",
                          as: "users",
                          cond: {
                            $eq: [
                              "$$users.user_id",
                              "$$ui._id"
                            ]
                          }
                        }
                      },
                      0
                    ]
                  }
                ]
              }
            }
          }
        }
      }
    ])
    

    Will output

    [
      {
        "_id": ObjectId("5d8b522d0cf2579c57bc8ce0"),
        "users": [
          {
            "_id": ObjectId("5d6f6d25e079b9fb7d858236"),
            "first_name": "Aaron",
            "is_finished": false,
            "last_name": "Potter",
            "user_id": ObjectId("5d6f6d25e079b9fb7d858236")
          },
          {
            "_id": ObjectId("5d8b522d0cf2579e27bc8ce3"),
            "first_name": "Harry",
            "is_finished": true,
            "last_name": "Paul",
            "user_id": ObjectId("5d8b522d0cf2579e27bc8ce3")
          }
        ]
      }
    ]
    

    Note : as proposed by @Valijon, you can add a $project stage if you need to slighty re-arrange from here.