Query to aggregate a field from one collection to another in MongoDB

Sai T 0
Viewed 22

I have these two collections.

collectionname : req

{
   reqId: "A123",
   status: "1",
   location: "hyd"
}

collection name : reqUser

{
   req: "A123"
   userId: "U1787"
   designation: "employee"
}

Need an aggregate filtering the req collection based on location as hyd & aggregating the user field based on reqId.

Need Like this:

{
   reqId: "A123",
   status: "1",
   userId: "U1787"
}

I used query on req collection with $match to filter based on location & $project to display only required fields from req collection and $lookup to match the reqId's from both collection but the issue is I am getting the whole reqUser object.

I am getting Something like this:

{
   reqId: "A123",
   status: "1",
   reqUser:[
   {
   req: "A123"
   userId: "U1787"
   designation: "employee"
   }
   ]
}

I need only userId field. Can anyone help me with obtaining the data as per my requirement mentioned above. I am using mongo version 3.4.

aggregate mongodb-query join database mongodb
1 Answers

Assuming req and reqUser has one-to-one relations, you could use the following query:

db.req.aggregate([
  {
    "$lookup": {
      "from": "reqUser",
      "localField": "reqId",
      "foreignField": "req",
      "as": "user"
    }
  },
  {
    $set: {
      userId: {
        $arrayElemAt: [
          "$user.userId",
          0
        ]
      }
    }
  },
  {
    "$project": {
      user: 0
    }
  }
])

Mongo playground ref: https://mongoplayground.net/p/V7bUdOXjnFC

Dezzley0
Related