I have the following data model:

"finances" : {
    "futurePostings" : [ 
        {
            "description" : "test",
            "orderId" : ObjectId("614702b9e98e83bc5d7d3d62")
        }
    ],
    "balance" : []
}

Then, I'm trying to move the element inside futurePosting to balance. I could remove it from futurePostings, but I can't figure out if would be possible to use the positional $ operator (or any other command) to push this same document inside balance, via the same query.

   db.collection.updateOne(
        {
          "finances.futurePostings.orderId": ObjectId(orderId),
        },
        {
          $push: { "finances.balance": ?? },
          $pull: {
            "finances.futurePostings": { orderId: ObjectId(orderId) },
          },
        }
      );

Is it possible?

CodePudding user response：

It is not possible in a regular update query, but you can try update with aggregation pipeline starting from MongoDB 4.2,

• pull from futurePostings
  • $filter to iterate loop of futurePostings array and check not equal to condition to remove provided orderId
• push into balance
  • $filter to iterate loop of futurePostings array and check equal to condition and filter matching orderId element
  • $concatArrays to concat current balance array and new element from above filtered result

db.collection.updateOne(
  { "finances.futurePostings.orderId": ObjectId(orderId) },
  [{
    $set: {
      "finances.futurePostings": {
        $filter: {
          input: "$finances.futurePostings",
          cond: {
            $ne: ["$$this.orderId", ObjectId(orderId)]
          }
        }
      },
      "finances.balance": {
        $concatArrays: [
          "$finances.balance",
          {
            $filter: {
              input: "$finances.futurePostings",
              cond: {
                $eq: ["$$this.orderId", ObjectId(orderId)]
              }
            }
          }
        ]
      }
    }
  }]
)

Playground