CODEWITHSUNDEEP
mongodb
dev007
dev007

Reputation: 49

How to join two collections based on the _id field in mongodb

I have two collections called orders and items as shown below. Now, I'm trying to join these collections based on the _id field. Can we use "$lookup" operator for this scenario? Or is there any other method to resolve this problem.

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
])


db.items.insert([
  { "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
  { "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
  { "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
])

Can anyone please help me out regarding this issue ...

Upvotes: 1

Views: 9175

Answers (2)

Sarus Crane
Sarus Crane

Reputation: 336

Try This.

db.orders.aggregate([
{ $lookup:
   {
     from: 'items',
     localField: '_id',
     foreignField: '_id',
     as: 'get_data'
   }
 } 

]).exec(function(err, res) {
if (err) throw err;
console.log(res);
});

Upvotes: 0

mickl
mickl

Reputation: 49945

Try following code:

db.orders.aggregate([
   {
     $lookup:
       {
         from: "items",
         localField: "_id",
         foreignField: "_id",
         as: "item"
       }
  },
  { $unwind: "$item" },
  {
    $project: {
        "_id": 1,
        "price": 1,
        "quantity": 1,
        "description": "$item.description",
        "instock": "$item.instock"
    }
  }
])

Since you know that there will be 1 to 1 relationship you can unwind $lookup results to have just one embedded item for each order. Then you can project your results to get flat structure of JSON. This will give you results in following shape:

{
    "_id" : 1,
    "price" : 12,
    "quantity" : 2,
    "description" : "almond clusters",
    "instock" : 120
}

Upvotes: 5

Related Questions