Update the Last Item of Nested Array By Adding to Its Own Value in MongoDB

user_2860565 0
Viewed 32

I'm trying to make a seemingly simple update to a MongoDB collection that looks like the below using Node.

Collection

{
  account_id: "ORG1",
  progress: [{week: 1, goal: 5000, raised: 2400}, {week: 2, goal:5100,  raised: 1000}]
}

The goal is to be able to

  1. Find the correct org (this works for me)
  2. Add a value to the last array entry's "raised" value. (e.g. Initially the raised value is 1000, after my update, it will be 1000 + an incoming value).

My hacky approach would be to do a find query to get the initial value and then do an update to add my incoming value to the initial value. But I'm sure there's a simpler way.

Thanks in advance!

mongodb-query node.js mongodb
3 Answers

One option is using an update pipeline:

  1. Split the array into the last item and all the rest.
  2. update the last item
  3. Build the array again using $concatArrays
db.collection.update(
  {account_id: "ORG1"},
  [
    {$set: {
      lastItem: {$last: "$progress"},
      rest: {$slice: ["$progress", 0, {$subtract: [{$size: "$progress"}, 1]}]}
    }
    },
    {$set: {"lastItem.raised": {$add: ["$lastItem.raised", incomingValue]}}},
    {$set: {
      progress: {$concatArrays: ["$rest", ["$lastItem"]]},
      lastItem: "$$REMOVE",
      rest: "$$REMOVE"
    }
  }
])

See how it works on the playground example - concatArrays

Another option is using $reduce:

Here we are iterating on the array, for each item checking if it is the last one, and if so updating it:

db.collection.update(
  {account_id: "ORG1"},
  [
  {$set: {lastIndex: {$subtract: [{$size: "$progress"}, 1]}}},
  {$set: {
      lastIndex: "$$REMOVE",
      progress: {
        $reduce: {
          input: "$progress",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {
                  $cond: [
                    {$eq: [{$size: "$$value"}, "$lastIndex"]},
                    {$mergeObjects: [
                       "$$this",
                        {raised: {$add: ["$$this.raised", incomingValue]}}
                      ]
                    },
                    "$$this"
                  ]
                }
              ]
            ]
          }
        }
      }
    }
  }
])

See how it works on the playground example - reduce

user_184823100

One way to do it is by using "$function" in the update/aggregation pipeline.

db.collection.update({
  "account_id": "ORG1"
},
[
  {
    "$set": {
      "progress": {
        "$function": {           // your increment value goes here ⮯
          "body": "function(prog) {prog[prog.length - 1].raised += 75; return prog}",
          "args": ["$progress"],
          "lang": "js"
        }
      }
    }
  }
])

Try it on mongoplayground.net.

user_14093740

I ended up finding a solution that feels a bit simpler than the solutions I've seen.

I added a field to the db called "status":

{
  account_id: "ORG1",
  progress: [{week: 1, goal: 5000, raised: 2400, status: historic}, {week: 2, goal:5100,  raised: 1000, status: current}]
}

Then I ended up using the positional operator ($) and increment functions.

const collection = client.collection(goalCollection);

 // Search Query
const query = {
  'account_id': ORG1,
  'progress.status: "current"
};

// Update with increment
const update = {
   $inc: {"progress.$.raised": ADDITIONAL_SUM}
}


// Run the update
const result = await collection.updateOne(query, update);

Worked smoothly.

user_28605650
Related