i am trying to carry a simple update query operation with MongoDB from my node js application that will run every night using node-cron but i haven't been able to get the update operation to work

.documents in my db look like

[
  {
    Balance: 4000,
    name: "Steph curry",
    password: "*****",
    created_At: ISODate("2022-04-19T07:17:29.243Z"),
    deposits: [
      {
        amount: 1000,
        paid: false,
        expiry: 28903708478, // this should be a timestamp
        credit: 150
      },
      {
        amount: 1000,
        paid: false,
        credit: 100,
        expiry: 28903708478 // this should be a timestamp
      }
    ]
  }
]

i want to query for all users where their deposit has expired (that is Date.now() > expiry ) and their paid value is false and then add the credit to their balance value, then turn the paid value to true.

/ basically what i want is something like this

db.collection.update({
  "deposits.paid": false,
  "deposits.expiry": { $lt: "$$NOW" }
},
{
  ballance: {
    $add: [ "deposits.$.credit", "$balance" ]
  },
  "deposits.$.paid": true
})

CodePudding user response：

I don't think your expiry is a valid timestamp (28903708478=2885/12/2 Sunday 15:54:38), so convert it yourself.

$map

$add

$cond

$mergeObjects

db.collection.update({
  "deposits.paid": false,
  "deposits.expiry": { $lt: 28903708479 }
},
[
  {
    $set: {
      Balance: {
        $add: [
          "$Balance",
          {
            $sum: {
              $map: {
                input: "$deposits",
                as: "d",
                in: {
                  $cond: {
                    if: {
                      $and: [
                        { $not: "$$d.paid" },
                        { $lt: [ "$d.expiry", 28903708479 ] }
                      ]
                    },
                    then: "$$d.credit",
                    else: 0
                  }
                }
              }
            }
          }
        ]
      }
    }
  },
  {
    $set: {
      deposits: {
        $map: {
          input: "$deposits",
          as: "d",
          in: {
            $mergeObjects: [
              "$$d",
              {
                paid: {
                  $cond: {
                    if: {
                      $and: [
                        { $not: "$$d.paid" },
                        { $lt: [ "$d.expiry", 28903708479 ] }
                      ]
                    },
                    then: true,
                    else: false
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
],
{
  multi: true
})

mongoplayground