MongoDB : re-shape a document using the properties of an object inside the same document-CodePudding

I have a collection as follows

{
  "name": "Sherlock Holmes",
  "address": {
     "house":"221b",
     "street":"Baker St",
     "city":"London",
  }
},
{
  "name": "James Bond",
  "address": {
     "door_number":"30",
     "address1":"Wellington Square",
     "city":"London",
  }
}

I would like to loop through each property in address and create a separate fields in main document. That is after executing the command the collection should look like as follows

{
  "name": "Sherlock Holmes",
  "house":"221b",
  "street":"Baker St",
  "city":"London",
},
{
  "name": "James Bond",
  "door_number":"30",
  "address1":"Wellington Square",
  "city":"London",
}

The key of address object is different for different documents. What is the best way to achieve it using command line ? I have tried like below

db.employees.find().forEach(function(obj) {
for (var key in obj.address) {
      if (obj.address.hasOwnProperty(key)) {
            db.employees.update({ '_id': obj._id }, {
                '$set': { key: obj.address[key] }
            });
      }
    }
});

But it adds a field with name key only.

CodePudding user response：

You can use $mergeObjects to construct your expected output. Then use $replaceRoot to wrangle the result.

db.collection.update({},
[
  {
    "$addFields": {
      "merged": {
        "$mergeObjects": [
          {
            // fields that you want to keep in outer
            _id: "$_id",
            name: "$name"
          },
          "$address"
        ]
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$merged"
    }
  }
],
{
  multi: true
})

Here is the Mongo Playground for your reference.