在 MongoDB 中使用 $lookup [英] Using $lookup in MongoDB

问题描述

我在 mydb 数据库中有以下集合:

I have the following collections in mydb database:

• purchases:它包含以下格式的文档:

• 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:它包含以下格式的文档:

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

其中名称是购买集合中客户的名称，成本是ALL的成本他购买的物品.

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

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

WHERE purchase.purchasedItems.i_name=sales.i_name

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.

推荐答案

所以这里有一些不正确的地方.

So there are couple of things incorrect here.

在 $lookup 阶段之后，一堆缺失的引用和缺失的 $unwind.

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

试试

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

没有$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"}
])

这篇关于在 MongoDB 中使用 $lookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！