Join multiple table in mongodb to get array of id values from another table-CodePudding

I am trying to join two table and get some additional data based on the array of ids from one table to other using mongodb. One collection related to "User" and another one is "Order". User collection contains an array of ids related to order and the Order table contains Order name and other data. I need to list all users with all order name and data with user details.

Eg:

User Collection:

User = [{
_id: ObjectId,
userId: 1,
userName: "User1",
OrderIds: [{oId: 1},{oId:3}]
},
{
_id: ObjectId,
userId: 2,
userName: "User2",
OrderIds: [{oId: 2},{oId:3}]
}]
--------------------------------------------
Order Collection:

Order = [{
_id: ObjectId,
orderId: 1
orderName: "Pen"
},
{
_id: ObjectId,
orderId: 2
orderName: "Book"
},
{
_id: ObjectId,
orderId: 3
orderName: "Chair"
}]
---------------------------------------------------

Desired Output:
-----------------------------------------------------
Output = [{
_id: ObjectId,
userId: 1,
userName: "User1",
OrderIds: [{oId: 1,oName: "Pen"},{oId:3,oName: "Chair"}]
},
{
_id: ObjectId,
userId: 2,
userName: "User2",
OrderIds: [{oId: 2,oName: "Book"},{oId:3,oName: "Chair"}]
}]

Please anyone have the idea, that how to do this?

CodePudding user response：

Use $lookup

db.users.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "OrderIds.oId",
      "foreignField": "orderId",
      "as": "OrderIds"
    }
  }
])

mongoplayground