Get info from another collection based of _id and merge - MongoDB

Question

I have two collections. Lets call one baskets and the other one fruits.

In baskets we have the following document:

[{
    basket_name: "John's Basket",
    items_in_basket: [
        {
            fruit_id: 1,
            comment: "Delicious!"
        },
        {
            fruit_id: 2,
            comment: "I did not like this"
        }
    ]
}]

And in fruits we have the following documents:

[{
    _id: 1,
    fruit_name: "Strawberry",
    color: "Red"
},
{
    _id: 2,
    fruit_name: "Watermelon",
    color: "Green"
}]

How do I get information on each fruit in John's Basket?

The result should look like this:

[{
    fruit_id: 1,
    comment: "Delicious!",
    fruit_name: "Strawberry",
    color: "Red"
},
{
    fruit_id: 2,
    comment: "I did not like this",
    fruit_name: "Watermelon",
    color: "Green"  
}]

There's no "join" in MongoDB. You either could consider using a MapReduce function to create a new structure, or write the code necessary to fetch each `fruit` instance on demand and merge it in your client code with a `basket` document. You might find [this](http://stackoverflow.com/questions/4067197/mongodb-and-joins) Q/A helpful. — WiredPrairie, Apr 08 '13 at 14:47
@WiredPrairie: this should be an answer not a comment, i would upvote that ;-) — hereandnow78, Apr 08 '13 at 15:23

score 4 · Accepted Answer · edited May 23 '17 at 11:59

There's no "join" in MongoDB. You either could:

consider using a MapReduce function to create a new structure that contains the merged data
write the code necessary to fetch each fruit instance on demand and merge it in your client code with a basket document.
denormalize the data and include the details for each fruit in the basket document. This poses it's own set of issues as data is duplicated and updates to a particular fruit would then need to be made to every usage in the collection.

Both have their pros and cons.

You might find this Q/A helpful, and also this documentation for MongoDB.

score 3 · Answer 2 · edited Nov 04 '20 at 01:56

this is no longer true.

Since version 3.2, MongoDB added the $lookup command.

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

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

db.inventory.insert([
   { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
   { "_id" : 5, "sku" : null, description: "Incomplete" },
   { "_id" : 6 }
])


db.orders.aggregate([
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
])

returns:

{
   "_id" : 1,
   "item" : "almonds",
   "price" : 12,
   "quantity" : 2,
   "inventory_docs" : [
      { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
   ]
}
{
   "_id" : 2,
   "item" : "pecans",
   "price" : 20,
   "quantity" : 1,
   "inventory_docs" : [
      { "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
   ]
}
{
   "_id" : 3,
   "inventory_docs" : [
      { "_id" : 5, "sku" : null, "description" : "Incomplete" },
      { "_id" : 6 }
   ]
}

you can't use `$lookup` on sharded collections. Use it only if your collections are unsharded. https://stackoverflow.com/questions/34633111/mongodb-to-use-sharding-with-lookup-aggregation-operator has a better solution for sharded collections. — Tom Bombadil, Mar 30 '21 at 16:14

Get info from another collection based of _id and merge - MongoDB

2 Answers2