CODEWITHSUNDEEP
javascriptmongodbaggregation-framework
fpena06
fpena06

Reputation: 2426

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.

Thanks in advance.

Upvotes: 0

Views: 7747

Answers (2)

Logan Cundiff
Logan Cundiff

Reputation: 553

The current answer above contains outdated information.

Mongo can update a collection by using $out which will take your query and output it to a collection you specify, so if you specify the collection you aggregate it will update it. So you can add a value to your embedded array and then output that new collection, thereby adding a new value to your array.

Mongo can do joins although I would avoid when possible. You can use $lookup within the aggregate to get info from other collections. You can also use Mongoose and use their populate function if your curious. NOTE: you cannot include $out within the $lookup pipeline, so put $out at the end of the overall aggregate.

You can combine these 2 principles by:

db.workorders.aggregate({
$match:{"Status":"Closed"}},
{$unwind:"$lines"},
{$match:{"lines.Status":"Closed"}},
{
   $lookup:
     {
       from: Prices,
       localField: lines.price,
       foreignField: price,
       as: lines.price
     }
},
{
   $lookup:
     {
       from: Product,
       localField: lines.Product,
       foreignField: Product,
       as: lines.Product
     }
},
{ $output: "workorders" } // this will replace your collection with output from aggregate

Upvotes: 1

Neil Lunn
Neil Lunn

Reputation: 151210

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.

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.

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.

Upvotes: 3

Related Questions