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

Question

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?

YuTing · Accepted Answer · 2021-10-13 13:47:59Z

2

Use $lookup

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

mongoplayground

answered Oct 13, 2021 at 13:47

YuTing

6,6692 gold badges8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lineesh Mb Over a year ago

Its working..Thank you :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related