Update Property of an Array Element in an Aggregration Pipeline

Aiko_Jansen · 2025-01-28T08:29:36.025Z

Hello everyone,
I have been struggling with this for a while now. I could not find a definite answer in the documentation, on why my initial approach should not work.

Maybe I am understanding and using the SDK wrong, or maybe there is a better way of achieving this.

I am using the NodeJS SDK and all examples are in JS.

I have an array of objects, something like:

{
    array: [
      { name: "Obj1" }, 
      { name: "Obj2" }, 
      { name: "Obj3" }
    ],
}

I want to

(a) update the property of a single array element and
(b) after the update insert a new element into the array.

Ideally, I would like to do this on one update operation, to save round-trips and to avoid beeing in an inconsistent state.

What I found is, that updating works fine, as long as I do not use an aggregation:

const { insertedId } = await col.insertOne({
    array: [{ name: "Obj1" }, { name: "Obj2" }, { name: "Obj3" }],
});

await col.updateOne(
  {
     _id: insertedId,
  },
  [
    {
      $set: { "array.1.name": "Update second array element only" },
    },
  ],
);

The snippet above works fine and only updates the second array element. BUT if I want to do the same in an aggregation, it does not work anymore:

const { insertedId } = await col.insertOne({
  array: [{ name: "Obj1" }, { name: "Obj2" }, { name: "Obj3" }],
});

await col.updateOne(
  {
    _id: insertedId,
  },
  [
    {
      $set: { "array.1.name": "Update second array element only" },
    },
  ],
);

This statement will update every field in the array, create a new object with the name 1 and a property called name:

{
  "_id": "67939109ca5e4686dc8f2c91",
  "array": [
    {
      "1": {
        "name": "Update second array element only"
      },
      "name": "Obj1"
    },
    {
      "1": {
        "name": "Update second array element only"
      },
      "name": "Obj2"
    },
    {
      "1": {
        "name": "Update second array element only"
      },
      "name": "Obj3"
    }
  ]
}

The only alternative that I have found, is to use a combination of $arrayElemAt with $mergeObject to select the element in question, construct a new object containing the desired changes and then use $concantArray and $slice to construct a new array:

const { insertedId } = await col.insertOne({
    array: [{ name: "Obj1" }, { name: "Obj2" }, { name: "Obj3" }],
  });

  await col.updateOne(
    {
      _id: insertedId,
    },
    [
      {
        $set: {
          array: {
            $concatArrays: [
              [
                {
                  $mergeObjects: [
                    { $arrayElemAt: ["$array", 0] },
                    { name: "Update first entry only" },
                  ],
                },
              ],
              { $slice: ["$array", 1, { $size: "$array" }] },
            ],
          },
        },
      },
    ],
  );

This solution seems to be very complicated, hard to read, hard to extend, and gets very complicated, if you work with nested arrays, i.e. doing an update in an array in an object of arrays.

I find this very confusing, why does the $set behave differently, depending on whether it is part of an aggregation or not.

Is this desired behavior?
Is there a better way to achieve the same result?

Any help would be appreciated.

Best Regards
Aiko

Note: this is a re-upload of my first post: How to Update Properties of a single Array Element.. Same content, but the formatting was screwed and I have found no way to edit the existing post.

steevej · 2025-02-01T14:30:26.256Z

Aiko_Jansen:

this is a re-upload of my first post

Thanks for publishing anew. Much easier to work with.

Aiko_Jansen:

I would like to do this on one update operation, to save round-trips and to avoid beeing in an inconsistent state.

I second that.

Aiko_Jansen:

This solution seems to be very complicated, hard to read, hard to extend, and gets very complicated

I agree.

You may try to do it in a single round trip, but with 2 updates in 1 bulkWrite. Both updates would specify the same filter. The first would $set using the non-aggregation syntax. The second would $push the new element.

bulk = [ { updateOne : {
    filter : { "_id" : insertedId } ,
    update : { $set : { "array.1.name" : "bar" } }
  } } ,
  { updateOne : {
    filter : { "_id" : insertedId } ,
    update : { $push : { "array" : { "name" : "steevej" } } }
  } } 
]

aneroid · 2025-02-02T06:36:53.381Z

Aiko_Jansen:

The snippet above works fine and only updates the second array element. BUT if I want to do the same in an aggregation, it does not work anymore

Your first and second updateOne queries are identical. I think the first one isn’t supposed to use the aggregation syntax and was actually meant to be:

const { insertedId } = await col.insertOne({
  array: [{ name: "Obj1" }, { name: "Obj2" }, { name: "Obj3" }],
});

await col.updateOne(
  {
    _id: insertedId,
  },
  {
    $set: { "array.1.name": "Update second array element only" },
  },
);

Aiko_Jansen:

This solution seems to be very complicated, hard to read, hard to extend, and gets very complicated, if you work with nested arrays, i.e. doing an update in an array in an object of arrays.

Fully agreed. But I don’t know if the issue is with $set specifically or aggregations in general.

To fully appreciate the complicated-ness of it, if you were inserting at the 5th position of a longer array, you would need $concat: [slice-before, $merge: [updated-elem], slice-after]:

[
  {
    $set: {
      array: {
        $concatArrays: [
          { $slice: ["$array", 0, 5] },
          [
            {
              $mergeObjects: [
                { $arrayElemAt: ["$array", 5] },
                { name: "Update fifth entry only" },
              ],
            },
          ],
          { $slice: ["$array", 6, { $size: "$array" }] },
        ],
      },
    },
  },
]

There are several operators that have different behaviours in aggregations vs updates, or just aren’t available. Like $push as an array operator just not being available in aggregations (in updates agg pipelines and agg queries). For aggregations, it’s only available in $bucket, $bucketAuto, $group, $setWindowFields.

Same goes for Array $addToSet vs Agg $addToSet.

Well, same goes for all Array update operators. Either not available in aggregations or have a completely different behaviour.

steevej:

You may try to do it in a single round trip, but with 2 updates in 1 bulkWrite .

This is a good workaround but is only applicable for cases where “I want to update a single element in an array”. It doesn’t work for situations where you have a multi-stage aggregation pipeline where moving out one update op is impractical (and possibly incorrect).

steevej · 2025-02-02T15:32:19.443Z

aneroid:

I think the first one isn’t supposed to use the aggregation syntax

I think so too. So I assumed it was a simple cut-n-paste issue since it was shared with the appropriate syntax in the first post linked by the author. It is a good thing you mentioned it as some readers might not have seen it.

aneroid:

This is a good workaround but is only applicable for cases where “I want to update a single element in an array”.

Agree. It is a workaround and like most workaround it is only applicable for the bad situation you try to “work around”.

In this case, I understood that the author really wanted to do a $set on 1 element and a $push in the same array. And this cannot be done with a single update since you get a write exception as seen in this playground.

But this use-case occurs quite often. For example, in a tasks array, you would want to set the end_date of the current task and push a new task in the array.

aneroid · 2025-02-02T16:34:51.604Z

steevej:

In this case, I understood that the author really wanted to do a $set on 1 element and a $push in the same array.

Oh yes, for sure this is a good workaround for the given scenario. I was merely reflecting upon my own challenges working on aggregation pipelines and the differing behaviour of some operators. Which also resulted in pipelines with large, deeply nested, complicated, hard to maintain stages for those operations.

I had intended to raise a bunch of Feature Requests; but it eventually fell off my radar.

steevej · 2025-02-11T01:54:43.312Z

@Aiko_Jansen, more than a week since you got feedback from 2 of us.

I, for one, would really appreciate a follow up from you.

Thanks

Aiko_Jansen · 2025-02-13T08:15:37.304Z

Hello everyone,
I am very sorry, somehow the MongoDB forum and I don’t get along The email notification about your replies got stuck in the Spam folder…

Thank you very much! Your responses were very helpful.

I understand, this is a weakness of MongoDB, working with nested arrays is harder then it could be - Thank you, for clarifying, that I did not miss something!

The workaround with a bulk write is a great idea, thanks for that. It does simplify the update a lot. But this raises another question:

If I understood correctly, updates do a document are atomic, even if they come from an aggregation pipeline (https://www.mongodb.com/docs/manual/core/write-operations-atomicity/#atomicity-and-transactions).

Would we loose this property with the bulk-write solution? Because albeit being only one round-trip, it would be to update operations? Giving us a very very small time window, where another write could happen, that then updates the wrong element? Probably more of a theoretical problem, than a real one, as I assume the writes should be executed in direct sequence.

Best regards
Aiko

steevej · 2025-02-13T13:11:18.706Z

Thanks for the update.

You could always wrap the bulk update within a transaction.

Aiko_Jansen · 2025-02-13T13:37:56.111Z

Good point. Thank you!