How to remove arrays inside array if a condition is met-CodePudding

I have an object schema that looks like this:

{
   "_id":"ObjectId(""30t00594537da2r7awe083va"")",
   "balances":{
      "intraday":[
         [
            1630939075734,
            1899.09
         ],
         [
            1630939435939,
            1899.32
         ],
         [
            1632306730756,
            0
         ],
         [
            1632306759376,
            0
         ],
         [
            1632272012916,
            1372.22
         ]
      ]
   }
}

I want to remove all arrays within "balances.intraday" array whose the second element is equal to 0.

so my desired array will looks like this:

  {
   "_id":"ObjectId(""30t00594537da2r7awe083va"")",
   "balances":{
      "intraday":[
         [
            1630939075734,
            1899.09
         ],
         [
            1630939435939,
            1899.32
         ],
         [
            1632272012916,
            1372.22
         ]
      ]
   }
}

I tried to use the $pull command but it only removes the index and not the whole array.

======================= Edit =======================

Thanks to Tom Slabbaert the solution is as follows :

db._get_collection().update_many(
{},
[
  {
    "$set": {
      "balances.intraday": {
        "$filter": {
          "input": "$balances.intraday",
          "cond": {
            "$ne": [
              {
                "$arrayElemAt": [
                  "$$this",
                  1
                ]
              },
              0
            ]
          }
        }
      }
    }
  }
])

CodePudding user response：

You should use pipelined updates for this, like so:

db.collection.updateMany(
{},
[
  {
    $set: {
      "balances.intraday": {
        $filter: {
          input: "$balances.intraday",
          cond: {
            $ne: [
              {
                $arrayElemAt: [
                  "$$this",
                  1
                ]
              },
              0
            ]
          }
        }
      }
    }
  }
])

Mongo Playground