Use a field's value and add it to a new field in an array - Mongodb

Question

I am attempting to turn this

{
   "fooList" : [
      {
         "bar" : {
            "baz" : 100
         }
      },
      {
         "bar" : {
            "baz" : 200
         }
      }
   ]
},
{
   "fooList" : [
      {
         "bar" : {
            "baz" : 300
         }
      },
      {
         "bar" : {
            "baz" : 400
         }
      }
   ]
}

into this:

{
   "fooList" : [
      {
         "baz" : 100,
         "bar" : {
            "baz" : 100
         }
      },
      {
         "baz" : 200,
         "bar" : {
            "baz" : 200
         }
      }
   ]
},
{
   "fooList" : [
      {
         "baz" : 300,
         "bar" : {
            "baz" : 300
         }
      },
      {
         "baz" : 400,
         "bar" : {
            "baz" : 400
         }
      }
   ]
}

As you can see, I really just copy baz and its value out of bar, but my issue is that is occurs within an array.

db.getCollection(<collection_name>).updateMany(
    {}, 
    { $set: { 'fooList.$[element].baz' : '$fooList.$[element].bar.baz' } },
    { upsert: true ,
      arrayFilters: [{'element.bar' : { $exists : true }}]
    }
)

But this will only set the string $fooList.$[element].bar.baz into baz, seen here through the results

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "fooList": [
      {
        "bar": {
          "baz": 100
        },
        "baz": "$fooList.$[element].bar.baz"
      }
    ]
  }
]

Can anyone tell me what I might be doing wrong, or if this is even possible? Thanks

Answer 1

You can do it using aggregation pipeline in the update in mongoDB 4.2+ as follow:

db.collection.update({},
[
{
$set: {
  fooList: {
    $map: {
      input: "$fooList",
      in: {
        $mergeObjects: [
          "$$this",
          {
            $cond: [
              {
                $ne: [
                  "$$this.bar.baz",
                  undefined
                ]
              },
              {
                baz: "$$this.bar.baz"
              },
              {}
            ]
          }
        ]
       }
      }
     }
    }
   }
  ],
 {
    multi: true
 })

Explained:

1. You $set $map-ping the array documents and merge based on condition where the nested element "bar.baz" exist , then you add object baz equal to the value taken from bar.baz.
2. Add multi:true to be done for all documents matching the update query filter or you can use updateMany()

playground

Unfortunatelly arrayFilters are not possible when you use update with aggregation pipeline ...

For earlier mongodb versions it will look something like this:

   db.collection.find({}).forEach(function(d){ var newfooList=[];d.fooList.forEach(function(s){ s["baz"]=s["bar"]["baz"]; printjson(s);newfooList.push(s);  }); d["fooList"]=newfooList ; db.collection.save(d) })

Answer 2

Here is a variation that demonstrates the v4.4 "merge onto self" capability that turns aggregate into an update. This is a useful approach when you want to process all the docs because it eliminates the "no filter" ({}) and {multi:true} bits required for update.

db.foo.aggregate([
    // The $map/$mergeObjects is the SAME as the answer above, just with a little
    // more compact format.  It is import to $project here, NOT $addFields, because
    // we seek to limit the pipeline to just _id (comes along automatically) and 
    // the fooList:
    {$project: {
        fooList: {$map: {
            input: "$fooList",
                in: {$mergeObjects: [
                        "$$this",
                        {$cond: [
                            {$ne: ["$$this.bar.baz", undefined]},
                            {baz: "$$this.bar.baz"},
                            {}  // else empty object                                          
                        ]}
                     ]}
        }}
    }}

    //  ...and now, using _id as the key (fast!), merge fooList back into doc:
    ,{$merge: {
        into: "foo",
        on: [ "_id" ],
        whenMatched: "merge",
        whenNotMatched: "fail"
    }}
]);