Using $lookup in MongoDB

Question

I have the following collections in mydb database:

purchases: it contains documents in the following format:

{
 _id: <ObjectId>,
 name: <String>, //customer name
 purchasedItems: <Array>
          0: < Object >
              i_name: <String> // item name
              qntity: <Int>
          // other objects in the array
}

sales: it contains documents in the following format:

{
 _id: <ObjectId>,
 i_name: <String>,
 qntity: <Int>
 cost: <Int>
}

I'd like to output a new collection that contains the following documents:

    {
     _id: <ObjectId>,
     name: <String>,
     cost: <Int>
    }

Where name is the name of the customer in the purchases collection and cost is the cost of ALL the items that he purchased.

Formally, each item's cost is defined as:

purchases.purchasedItems.qntity/sales.qntity) * sales.cost

WHERE purchases.purchasedItems.i_name=sales.i_name

and cost in the output collection is the sum of all the items' cost.

I've tried the following but it doesn't work:

db.purchases.aggregate([
    {$unwind: "$purchasedItems"},
    {$lookup:
        {from:"sales",
        localField:"purchasedItems.i_name",
        foreignField:"i_name",
        as: "n_cost"}
    },
    {
        $group:{
               _id: "$_id",
               name: "$name",
               cost: {$sum: {multiply:[{$divide:["$n_cost.qntity","$qntity"]},"$n_cost.cost"]}}
        }
    },
    {$out: "results"}
])

I'd appreciate any help with what I did wrong and what's the correct way to do it.

I get null on the name field and 0 on count after using name:{$first:"$name"} — Loay, Jan 27 '18 at 18:11

s7vr · Accepted Answer · 2018-01-27T18:43:22.670

So there are couple of things incorrect here.

Bunch of missing reference and missing $unwind after $lookup stage.

Try

db.purchases.aggregate([
  {"$unwind":"$purchasedItems"},
  {"$lookup":{
    "from":"sales",
    "localField":"purchasedItems.i_name",
    "foreignField":"i_name","as":"n_cost"
  }},
  {"$unwind":"$n_cost"},
  {"$group":{
    "_id":"$_id",
    "name":{"$first":"$name"},
    "cost":{
      "$sum":{
        "$multiply":[
          {"$divide":["$purchasedItems.qntity","$n_cost.qntity"]},
          "$n_cost.cost"
        ]
      }
    }
  }},
  {"$out":"results"}
])

Without $unwind

db.purchases.aggregate([
  {"$lookup":{
    "from":"sales",
    "localField":"purchasedItems.i_name",
    "foreignField":"i_name",
    "as":"n_cost"
  }},
  {"$project":{
    "name":1,
    "cost":{
      "$sum":{
        "$map":{
          "input":{"$range":[0,{"$size":"$purchasedItems"}]},
          "as":"ix",
          "in":{
            "$let":{
              "vars":{
                "purchase":{"$arrayElemAt":["$purchasedItems","$$ix"]},
                "sales":{"$arrayElemAt":["$n_cost","$$ix"]}},
                "in":{
                  "$multiply":[
                    {"$divide":["$$purchase.qntity","$$sales.qntity"]},"$$sales.cost"
                  ]
                }
            }
          }
        }
      }
    }
  }},
  {"$out":"results"}
])

Thanks a lot! I've tried the solution with the unwind option and it works! — Loay, Jan 27 '18 at 18:44

Using $lookup in MongoDB

1 Answers1

Linked