更新mongodb中的汇总结果？ [英] update aggregate results in mongodb?

问题描述

考虑以下用于工作单收集的模式。

Considering the following schema for workorders collection.

    {"Activity #": "1111111",
    "Customer": "Last, First",
    "Tenure": "0 Year 2 Months",
    "Account #": "0000000",
    "lines": [{
            "Line#": "line#",
            "Product": "product",
            "Status": "status",
            "price": "price"
        },
        {
            "Line#": "line#",
            "Product": "product",
            "Status": "status",
            "price": "price"
        }]}

我可以获取所有封闭的订单项

I can get all my closed line items with the following query.

db.workorders.aggregate({$match:{"Status":"Closed"}},{$unwind:"$lines"},{$match:{"lines.Status":"Closed"}},{$project:{lines:1}})

现在可以说我还有另一个收藏价格。

Now lets say I have another collection prices.

    {"Product":"product", 
       "Price":"Price"}

如何通过匹配价格集合中的产品来更新封闭行项目的价格。

How can I update the closed lines items price by matching the product in the prices collection.

预先感谢。

推荐答案

因此，尽管您可以使用来获得结果聚合，这里的问题就是聚合的用途。因此，它用于查询结果。

So while you can get results from this using aggregate, the problem here is that is exactly what aggregate is for. So it is used to query results.

虽然确实过滤了所需数组中的匹配项，下一个问题是您一次不能更新一个数组元素。因此，为了更新多个项目，您唯一的选择是在更新期间替换整个阵列。除非您已经拥有数组的内容，否则您需要获取整个文档。

And while that does "filter" just the "matching" items in the array that you want, the next problem is that you cannot update more than one array element at a time. So in order to update "multiple" items, your only option is to replace the whole array during the update. And unless you already have the contents of the array, you need to fetch the entire document.

此处介绍的第三个问题是您需要查找另一个集合以匹配值。简而言之， MongoDB不执行联接。

The "third" problem you introduce here is that you need to "lookup" another collection in order to match values. Put simply, MongoDB does not do joins. So there that is.

除非您要更改架构以适应其他情况，否则您将陷入困境：

Unless you are changing your schema to suit otherwise, then you are stuck with this:

db.workorders.find({ "Status": "Closed", "lines.Status": "Closed" })
    .forEach(function(order) {
        for ( var i = 0; i < order.lines.length; i++ ) {
            if ( order.lines[i].Status == "Closed" ) {
               var prod = db.product.findOne(
                   { "product": order.lines[i].product });
               order.lines[i].price = prod.price;
            }
        }

        db.workorders.update(
            { "_id": order._id },
            { "$set": { "lines": order.lines } }
        );
})

语言实现。但这是这里的一般逻辑。

Or however that actually works out in your language implementation. But that is the general logic here.

因此，在这种情况下，原始的聚合语句对您不利。即使使用匹配的工作单，结果也有点多余，因为您仍然可以查询它。

So the original aggregate statement is not much good to you in this case. Even using the matching "workorders" that result are sort of redundant as you can just query that anyway.

这里有一个用法帮助稍微优化一下这一点，那就是更改汇总语句，以获取要更新的唯一产品。然后，您不必每次匹配一个产品时都调用 .findOne（），而是可以使用汇总中的结果来缓存要在其中击中的产品

There is one usage here that does help optimize this a little, and that is to alter the aggregate statement to get the unique products that you will be looking up to update. Then rather than call .findOne() every time you match a product, you could use the results from aggregate to "cache" the products that are going to be "hit" in the update.

var prodValues = db.workorders.aggregate([

    // Match the order 
    { "$match":{ "Status":"Closed" }},

    // Unwind the array
    { "$unwind":"$lines" },

    // Match only the closed lines
    { "$match" :{ "lines.Status":"Closed" } },

    // Group the products
    { "$group": {
        "_id": null,
        "products": { "$addToSet": "$lines.Product" }
    }}
])

然后，您可以将其一次输入到查询中为了获得价格缓存：

Then you can feed that into your query for products once in order to get a "price cache":

var prodCache = {};

db.products.find({ "Product": { "$in": prodValues.result.products } })
    .forEach(function(product) {
        prodCache[product.Product] = product.price;
});

并用它代替每次查找。因此，围绕以下几行进行更改：

And use that in place of doing a find every time. So change around these lines:

            if ( order.lines[i].Status == "Closed" )
                order.lines[i].price = 
                    prodCache[order.lines[i].Product].price;

因此，至少这可以使您从汇总语句中受益。

So at least that gives you "some" benefit from your aggregate statement.

总体而言，当您必须更新数组中的多个项时，最好关闭整个数组。至少要等到下一个版本（在撰写本文时），才可以在批量更新。

Overall, when you have to update multiple items in an array, you are best off updating the entire array. At least until the next release (as of writing) when you can do that in batch updates.

这篇关于更新mongodb中的汇总结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！