How add total value of a field as a pre-aggregated field in update time MongoDb?

user_4529373 0
Viewed 94

I know that using $inc in Mongo you can calculate and update the count of data in update time, such as:

db.flights.update(  
  {
     $inc: {totalCnt: 1}
     $push: { price: {'t': time, 'p': newValue },
     $setOnInsert: { flightNo: "EN120" , airline="LFT"}
  },
  { upsert: true }
)

However, I need to also calculate the total value of price.p (in above example) and add and store it as a field in update time. I wondering is it possible to use $sum or $add in update? (Actually I want to be able to store average price as pre-aggregated field in update time).

For example, suppose that this document exists in flights collection:

{ "_id" : 1, "flightNo" : "EN120", "airline" : "LFT", "price" : [{"t": 120, "p": 2500}]}

The result that I need after executing update (suppose new value for time and price is 130 and 3000 respectively) command is:

{ "_id" : 1, "flightNo" : "EN120", "airline" : "LFT", "price" : [{'t': 120, 'p': 2500}, {'t': 130, 'p': 3000}], "totalCnt":2, "totalPrice": 5500}

And after executing another update with t=150 and p=2850 the result should be:

{ "_id" : 1, "flightNo" : "EN120", "airline" : "LFT", "price" : [{'t': 120, 'p': 2500}, {'t': 130, 'p': 3000}, {'t': 150, 'p': 2850}], "totalCnt":3, "totalPrice": 8350}

Thanks

mongodb
1 Answers

For Mongo v4.2+ you can use pipelined update, mind you you'll have to change your updates structure like so:

db.collection.updateOne(
    { query },
    [
        {
            $set: {
                airline: {$ifNull: ["$airline", "LFT"]},
                flightNo: {$ifNull: ["$flightNo", "EN120"]},
                price: {$concatArrays: [{$ifNull: ["$price", []]}, [{'t': time, 'p': newValue}]]},
            }
        },
        {
            $set: {
                totalPrice: {
                    $reduce: {
                        input: "$price",
                        initialValue: 0,
                        in: {$sum: ["$$this.p", "$$value"]}
                    }
                },
                totalCnt: {$size: "$price"}
            }
        }
    ],
    {
        upsert: true
    }
)

For lesser versions you'll have to split it into 2 calls. first fetch, do the calculation in code and then update.

--- EDIT ---

A more efficient way of calculating totalPrice:

db.collection.updateOne(
    { query },
    [
        {
            $set: {
                flightNo: {$ifNull: ["$flightNo", "EN120"]},
                totalPrice: {$sum: ["$totalPrice", newValue]},
                price: {$concatArrays: [{$ifNull: ["$price", []]}, [{'t': time, 'p': newValue}]]},
            }
        },
        {
            $set: {
                totalCnt: {$size: "$price"}
            }
        }
    ],
    {
        upsert: true
    }
)
user_107703700
Related