Join in MongoDB on different conditions

Working with Data
1

Hi all,
I just want to know that can we use multiple columns join in $lookup as we do in sql like this

left join ABC abc
on
abc.X = def.T
and
abc.Y = def.U
and
abc.Z = def.V

2

Yes, here is example in the Docs

4

Can you do it for me, as here is the first collection:

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 }
])

And here is the second collection:

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

And i want the parallel to this:

select b.item, a.instock from inventory a
inner join orders b on
a.sku = b.item and
a._id = b._id;
5

A simple one-to-one mapping of the example from the link provided by Katya to your sample documents will give the following.(I left the $project stages out)

db.inventory.aggregate([
   {
      $lookup:
         {
           from: "orders",
           let: { inventory_id: "$_id", inventory_sku: "$sku" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$_id",  "$$inventory_id" ] },
                         { $eq: [ "$item", "$$inventory_sku" ] }
                       ]
                    }
                 }
              }
           ],
           as: "lookup"
         }
    }
])

I left the $project stages out as the important things are the let : { … } and the multiple $eq with the $and : [ … ].

However, I have some issues with this data model which looked already like an adaptation of the example provided from the link. (The choice of the item names almonds, pekans, … are a give away).

Issue 1: Clearly document orders with _id:2 is related to inventory document _id:4. Your query a._id = b._id will never pick it up.

Issue 2: In my book orders can have more that one item (sku). I would imagine to have an array items with an order.

2 Likes
6

Thanks a lot @steevej

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.