'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

mongodb


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions