Add new array element - Update with Aggregation pipeline

Working with Data
,
1

Hello team,

My query is related to mongo update with aggregation pipeline.

Input document:

{
"FieldA": [{"key" : "A", "value" : 1234},{"key" : "A", "value" : 5689}],
"FieldB": [{"key": "B", "value": 4567},{"key" : "B", "value" : 4532}]
}

FieldA and FieldB may/may not exist in the document during update. Array size is dynamic i.e., can be 1/2/many
If exists, add a new key-value pair to the array
If not exists, create a new array field with one key-value pair

Question 1:
How to add new element to an existing array or create a new array using update with aggregation pipeline. Since $push cannot be used in update pipeline. Alternates to achieve this?

Question 2:
The updates are triggered in batches. If there is a failure in updating a batch, undo the updates i.e.,
If array size is 1 - unset the keys “FieldA” and “FieldB”
If array size is greater than 1 - remove the last element with key = “A” for FieldA and key = “B” for FieldB
How to achieve this ?

I also update the value of an existing field to a new field using update with mongo aggregation. How to frame the queries for Question 1 and Question 2 and include them as part of update with mongo aggregation pipeline.

Regards,
Laks

2

Hi @Laks and welcome back :slight_smile: !

Why do you want to use the pipeline update when there is a more simple solution?

For your question 1, I would just do this:

 test [direct: primary] test> db.coll.insertOne({name: "Max"})
{
  acknowledged: true,
  insertedId: ObjectId("62a75465bf5bb07f017b7d7f")
}
test [direct: primary] test> db.coll.updateOne({name: "Max"}, {$push: {fieldA: {key: "A", value: 1234}}})
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
test [direct: primary] test> db.coll.findOne()
{
  _id: ObjectId("62a75465bf5bb07f017b7d7f"),
  name: 'Max',
  fieldA: [ { key: 'A', value: 1234 } ]
}
test [direct: primary] test> db.coll.updateOne({name: "Max"}, {$push: {fieldA: {key: "A", value: 5678}}})
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
test [direct: primary] test> db.coll.findOne()
{
  _id: ObjectId("62a75465bf5bb07f017b7d7f"),
  name: 'Max',
  fieldA: [ { key: 'A', value: 1234 }, { key: 'A', value: 5678 } ]
}

The $push update operator behaves as you described by default.

Question 2: The only way to achieve this properly is to wrap your batch in a multi-doc ACID transactions and abort the transaction if an error occurs in your processing.

Regarding your last question:

I’m not sure to understand. Can you elaborate or provide an example maybe?

Cheers,
Maxime.

3

Hi @MaBeuLux88

Thanks for your response.

Following are the fields to be updated in mongo document,

{
"FieldA": // add an array element or create new array
"FieldB": // add an array element or create new array
"FieldC": "$varC" // "varC" is an existing field in the document, the value of the existing field will be copied to this new field "FieldC"
}

Without aggregation pipeline, I will not be able to copy “FieldC” from an existing field for each document.
So I’m insisting on using updates with aggregation pipeline to make the work easier.

The below command worked for Question 1:

db.collection.updateMany({},
[{ "$set": {"FieldA":{"$cond" : [ {$isArray: "$FieldA" }, {"$concatArrays": [ "$FieldA",[{"key" : "V", "value": 5678}]  ] }, [{"key" : "V", "value": 5678}],
//same logic for "FieldB"
 ]
);

Question 2: I don’t wish to use transaction management, as the volume is really high and better option is to process them in batches rather than holding the related documents in memory. How can I perform undo for he updates made to FieldA, FieldB

4

Without the transaction you have no guarantee that you are undoing what you just updated. Maybe another update was done in the meantime by another system and you are going to alter this instead of what you meant to undo.

But if your batch is isolated, I think you could do this:

If array is size 1 => $$REMOVE.
If array is size 2+ => $slice.

Let’s say I want to “undo” 5689 and 4567:

db.coll.findOne()
{
  _id: ObjectId("62a76d0b2567cbdbd1ad3d03"),
  FieldA: [ { key: 'A', value: 1234 }, { key: 'A', value: 5689 } ],
  FieldB: [ { key: 'B', value: 4567 } ]
}

I can execute this:

db.coll.updateOne({}, [{
  "$set": {
    "FieldA": {
      "$cond": [{$eq: [{$size: "$FieldA"}, 1]}, "$$REMOVE", {"$slice": ["$FieldA", {"$add": [{$size: "$FieldA"}, -1]}]}]
    }, 
    "FieldB": {
      "$cond": [{$eq: [{$size: "$FieldB"}, 1]}, "$$REMOVE", {"$slice": ["$FieldB", {"$add": [{$size: "$FieldB"}, -1]}]}]
    }
  }
}])

Result:

db.coll.findOne()
{
  _id: ObjectId("62a77b1a2567cbdbd1ad3d04"),
  FieldA: [ { key: 'A', value: 1234 } ]
}

Cheers,
Maxime.

5

Thanks @MaBeuLux88. This was really helpful