I read some threads about it, such as this one and that one, but they both come with major flaws & caveats (to me at least).

What I want is simple! An update query that:

• swaps 2 elements in an array.
• each element is a sub-document.
• I am only given the two indexes as input, of those which need to be swapped.
• I want to AVOID using a javascript function, if at all possible.
• I want it to BE ATOMIC!

Here is the part i'm struggling with:

Option 1: Using update WITH aggregation

( summary: succeeds at $arrayElemAt, fails over at "images.0" )

db.users.update(
    { userID: 1 },
    [
        { $set: { 
            "images.0": { $arrayElemAt: ['$images', 2] },
            "images.2": { $arrayElemAt: ['$images', 0] },
        }}
    ]
)

If I use an aggregation in my update, I get access to the $arrayElemAt operator, which is great! But this solution fails because this → "images.0" doesn't do what I expect it too. I would have expected it to say "hey, please override the first element inside the images array". But no, what it does is it pushes a new field called "0": { ... }, to each and every sub-document inside the images array.

Option 2: Using update WITHOUT aggregation

( summary: succeeds at "images.0", fails over at $arrayElemAt)

db.users.update(
    { userID: 1 },
    { $set: { 
        "images.0": { $arrayElemAt: ['$images', 2] },
        "images.2": { $arrayElemAt: ['$images', 0] },
    }}
)

This one fails & succeeds exactly at the opposite places.
The "images.n" DOES replace the n'th element with the given input.
The problem here is, since it is no longer an aggregation, I have no access to the $arrayElemAt operator, and the new n'th element simply becomes:

{
    "$arrayElemAt" : [
        "$aboutMe.images",
        2.0
    ]
},

Does anyone know what can be done?

CodePudding user response：

This is a nice question that I didn't meet before. The catch here, is that you need a pipeline to refer existing values, but this prevents working with direct index like dot notation or even $push. Hence, one option is using $reduce:

1. $set keys for the wanted values.
2. $reduce with reference to the size of $$value, according to this answer, in order to replace.

db.users.update(
{userID: 1},
[
  {$set: {
      firstItem: {$arrayElemAt: ["$images",  2]},
      secondItem: {$arrayElemAt: ["$images", 0]}
    }
  },
  {$set: {
      images: {
        $reduce: {
          input: "$images",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$cond: [
                  {$eq: [{$size: "$$value"}, 0]},
                  ["$firstItem"],
                  {$cond: [{$eq: [{$size: "$$value"}, 2]}, 
                           ["$secondItem"],
                           ["$$this"]]
                  }
                ]
              }
            ]
          }
        }
      },
      firstItem: "$$REMOVE",
      secondItem: "$$REMOVE"
    }
  }
])

See how it works on the playground example