CODEWITHSUNDEEP
mongodb
Stan
Stan

Reputation: 400

Set a new field based on calculations in MongoDB update statement

I use the attribute pattern approach in my collection:

[
  {
    _id: "id1",
    _cells: [
      { k: "key1", v: "value1" },
      { k: "key2", v: "value2" },
      { k: "key3", v: "value3" },
    ]
  },
  // another records
]

I need a new field that will contain key information from _cells like this:

  {
    _id: "id1",
    new_field: "value1_value2",   // without key3
    _cells: [
      { k: "key1", v: "value1" },
      { k: "key2", v: "value2" },
      { k: "key3", v: "value3" },
    ]
  },

I'm looking for something like this but don't know how to do it correctly:

db.collection.updateMany(
   { },
   { $set: { new_field: { $concat: [ "$_cells.$[$_cells[k]=='key_1'].v", "$_cells.$[$_cells[k]=='key_2'].v" ] } } }
)

Update: New key was appended

Upvotes: 0

Views: 73

Answers (1)

turivishal
turivishal

Reputation: 36144

The $concat is an aggregation operator, it can not support normal queries, try update with aggregation pipeline starting from MongoDB 4.2,

You just need to wrap the update part in array bracket [],

  • $indexOfArray to find the array index of key1 key and same as key2 key
  • $arrayElemAt to get specific element's value from _cells array by passing above index
  • $concat to concat both the key's value by _ character
db.collection.updateMany({},
[
  {
    $set: {
      new_field: {
        $concat: [
          {
            $arrayElemAt: [
              "$_cells.v",
              { $indexOfArray: ["$_cells.k", "key1"] } // pass key1
            ]
          },
          "_",
          {
            $arrayElemAt: [
              "$_cells.v",
              { $indexOfArray: ["$_cells.k", "key2"] } // pass key2
            ]
          }
        ]
      }
    }
  }
])

Playground

Upvotes: 1

Related Questions