How do you update an object with arrayElemAt?

Working with Data
#1

I’m trying to write an update script to update a nested object. Here’s an example of the data structure I’m working with:

"results" : {
        "overallPerformance" : {

        }, 
        "item" : [
            {
                "itemNo" : NumberInt(1), 
                "response" : {
                    "answer" : "A"
                }, 
            }, 
            {
                "itemNo" : NumberInt(2), 
                "response" : {
                    "answer" : "B"
                }, 
            }, 
        ]
    },

“results” is just a single object that’s a part of the overall document. I’m trying to set a new field, that we’ll call ‘correlation’. I’m looping through a series of operations to calculate the ‘correlation’ and I’m creating a bulkWrites array along the way. Then at the end of the promise chain I execute the bulkWrites.

However the issue I’ve run into is how can I update the correct item? The loop I’m running through won’t be on every single item. The records can have 20, 30+ items in the items array, and I may just be updating itemNo 1, 3, 17, 24, etc. rather than every single one. I can easily filter the updateOne on the correct document itself with the id. But in the $set stage, I don’t know what to put to update the correct item. I thought about trying to use "$arrayElemAt": [ "$results.item.itemNo", itemNo ] but that can’t be in the $set stage I don’t think, because that’s just to get the correct array element, and has nothing to do with the new ‘correlation’ field I want to set.

Is there a way to do something like:

bulkWrites.push({
updateOne: {
   filter: {
      _id: doc._id
   },
   update: {
      $set: {
         // obviously here I want to be setting
         // correlation: myCalculatedCorrelation
         // but I just don't know how to do it
         // on the right item
         "results.item[itemNo - 1].correlation": myCalculatedCorrelation
      }
   }
}
})

Where/how I can input the variable itemNo to get the right array element? Or better yet, update the item where the item.itemNo equals the variable itemNo.

#2

Hi @Charlie_Buyas and welcome in the MongoDB Community :muscle: !

I think you are trying to use the $ array positional operator.

Here is the query:

db.coll.updateOne(
  {
    _id: ObjectId("622a45b426344874fd6526d8"),
    "results.item.itemNo": 2
  },
  {
    $set: {"results.item.$.correlation": 123}
  }
)

Here it is in action:

test [direct: primary] test> db.coll.insertOne({ "results": { "overallPerformance": {}, "item": [ { "itemNo": NumberInt(1), "response": { "answer": "A" } }, { "itemNo": NumberInt(2), "response": { "answer": "B" } }] } })
{
  acknowledged: true,
  insertedId: ObjectId("622a45b426344874fd6526d8")
}
test [direct: primary] test> db.coll.findOne()
{
  _id: ObjectId("622a45b426344874fd6526d8"),
  results: {
    overallPerformance: {},
    item: [
      { itemNo: 1, response: { answer: 'A' } },
      { itemNo: 2, response: { answer: 'B' } }
    ]
  }
}
test [direct: primary] test> db.coll.updateOne({_id: ObjectId("622a45b426344874fd6526d8"), "results.item.itemNo": 2},{$set: {"results.item.$.correlation": 123}})
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
test [direct: primary] test> db.coll.findOne()
{
  _id: ObjectId("622a45b426344874fd6526d8"),
  results: {
    overallPerformance: {},
    item: [
      { itemNo: 1, response: { answer: 'A' } },
      { itemNo: 2, response: { answer: 'B' }, correlation: 123 }
    ]
  }
}

Does that work for you?

Cheers,
Maxime.

1 Like
#3

Yes, thanks Maxime. I think the issue was that before, I was just matching on the _id and not also on the results.item.itemNo.

I didn’t know that including that in the match allowed the positional operator to know which nested object you wanted. Where in the docs can I find further info on this?

1 Like
#4

You have all the different array operators here, including the $ positional update operator.

Cheers,
Maxime.

2 Likes