MongoDB rename database field within array

Question

I need to rename indentifier in this:

{ "general" : 
  { "files" : 
    { "file" : 
      [  
        {  "version" : 
          {  "software_program" : "MonkeyPlus",      
             "indentifier" : "6.0.0" 
          } 
        } 
      ] 
    } 
  } 
}

I've tried

db.nrel.component.update(
  {},
  { $rename: {
    "general.files.file.$.version.indentifier" : "general.files.file.$.version.identifier"
  } },
  false, true
)

but it returns: $rename source may not be dynamic array.

Semiconscious

$rename does not expand arrays, doc – Swimming

@Alexander Azarov, any ideas on fixing this? i've heard of people copying to fields in which $rename can go... – Semiconscious

Personally I am writing scripts walking through the collection and doing migrations – Swimming

If you are looking to do this with database commands: How to rename a field inside an array with database commands? – Cozy

Answer 1

As mentioned in the documentation there is no way to directly rename fields within arrays with a single command. Your only option is to iterate over your collection documents, read them and update each with $unset old/$set new operations.

Snowy

Stuck with MongoDB... things like this make me sad :( – Moxa

I don't disagree. This is relatively low hanging fruit I'd imagine. – Snowy

This answer is outdated, see Sudhesh's answer which IMO is the best – Handlebar

Answer 2

For what it's worth, while it sounds awful to have to do, the solution is actually pretty easy. This of course depends on how many records you have. But here's my example:

db.Setting.find({ 'Value.Tiers.0.AssetsUnderManagement': { $exists: 1 } }).snapshot().forEach(function(item)
{    
    for(i = 0; i != item.Value.Tiers.length; ++i)
    {
        item.Value.Tiers[i].Aum = item.Value.Tiers[i].AssetsUnderManagement;
        delete item.Value.Tiers[i].AssetsUnderManagement;
    }
    
    db.Setting.update({_id: item._id}, item);
});

I iterate over my collection where the array is found and the "wrong" name is found. I then iterate over the sub collection, set the new value, delete the old, and update the whole document. It was relatively painless. Granted I only have a few tens of thousands of rows to search through, of which only a few dozen meet the criteria.

Still, I hope this answer helps someone!

Edit: Added snapshot() to the query. See why in the comments.

You must apply snapshot() to the cursor before retrieving any documents from the database. You can only use snapshot() with unsharded collections.

From MongoDB 3.4, snapshot() function was removed. So if using Mongo 3.4+ ,the example above should remove snapshot() function.

Primus

Imho this should be the accepted answer. I performed this approach on several collections with up to twelve million documents each and it worked like a charm! – Herrod

Absolutely. This works great. A pity MongoDB is not able yet to include this behaviour natively. – Hesperides

It's better to use snapshot(): db.Setting.find({ 'Value.Tiers.0.AssetsUnderManagement': { $exists: 1 } }).snapshot().forEach(...) Otherwise you can end up in an infinite loop. See also docs.mongodb.org/v3.0/reference/method/cursor.snapshot – Inadequate

New one to me. Thanks for the details, @PatrycjaK! Updating my answer. – Primus

@Andrew Samuelsen I think this should be the selected answer - a script to work around the "by design" problem – Stretcherbearer

@PatrycjaK: snapshot is deprecated as of MongoDB 4.0. – Dora

This no longer works in recent (don't know exact version numbers) versions of MongoDB, failing with the error 'MongoInvalidArgumentError: Update document requires atomic operators' – Silverfish

Thanks @domisum. Do you know of a workaround? It's odd because it looks like an atomic operation in the code. We're looping over items in a forEach loop and then calling a single update call per record. I'm not sure what's violating the atomic operator. Perhaps your update statement is incorrect and it's trying to target multiple documents?? – Primus

Answer 3

I had a similar problem. In my situation I found the following was much easier:

1. I exported the collection to json:

mongoexport --db mydb --collection modules --out modules.json

2. I did a find and replace on the json using my favoured text editing utility.

3. I reimported the edited file, dropping the old collection along the way:

mongoimport --db mydb --collection modules --drop --file modules.json

Nadler

I'm kinda skeptical how well this approach works on a large dataset. Say my db is 5gb, this process sounds slow. Or perhaps I'm not using the right text editing tools =-} – Litho

Also be cautious of live databases. If records get added/modified in the time it takes to do these steps, changes will be lost. – Primus

This is okay if you have something that only runs on your laptop but isn't a viable solution if you're working in a production environment. – Latency

Answer 4

Starting Mongo 4.2, db.collection.update() can accept an aggregation pipeline, finally allowing the update of a field based on its own value:

// { general: { files: { file: [
//   { version: { software_program: "MonkeyPlus", indentifier: "6.0.0" } }
// ] } } }
db.collection.updateMany(
  {},
  [{ $set: { "general.files.file": {
       $map: {
         input: "$general.files.file",
         as: "file",
         in: {
           version: {
             software_program: "$$file.version.software_program",
             identifier: "$$file.version.indentifier" // fixing the typo here
           }
         }
       }
  }}}]
)
// { general: { files: { file: [
//   { version: { software_program: "MonkeyPlus", identifier: "6.0.0" } }
// ] } } }

---

Literally, this updates documents by (re)$setting the "general.files.file" array by $mapping its "file" elements in a "version" object containing the same "software_program" field and the renamed "identifier" field which contains what used to be the value of "indentifier".

---

A couple additional details:

• The first part {} is the match query, filtering which documents to update (in this case all documents).

• The second part [{ $set: { "general.files.file": { ... }}}] is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline):

  • $set is a new aggregation operator which in this case replaces the value of the "general.files.file" array.
  • Using a $map operation, we replace all elements from the "general.files.file" array by basically the same elements, but with an "identifier" field rather than "indentifier":
  • input is the array to map.
  • as is the variable name given to looped elements
  • in is the actual transformation applied on elements. In this case, it replaces elements by a "version" object composed by a "software_program" and a "identifier" fields. These fields are populated by extracting their previous values using the $$file.xxxx notation (where file is the name given to elements from the as part).

Ontine

You were keep software_program manually. But what if version field has alot of children fields, and may be difference fields in each documents? – Strohbehn

To answer @HaidangNguyen's question, and in general a good practice - use $mergeObjects in $map's in :) In the case of the example above: in: { $mergeObjects: ['$$file', / * changes here */] }. – Handlebar

Answer 5

I had to face the issue with the same schema. So this query will helpful for someone who wants to rename the field in an embedded array.

db.getCollection("sampledocument").updateMany({}, [
  {
    $set: {
      "general.files.file": {
        $map: {
          input: "$general.files.file",
          in: {
            version: {
              $mergeObjects: [
                "$$this.version",
                { identifer: "$$this.version.indentifier" },
              ],
            },
          },
        },
      },
    },
  },
  { $unset: "general.files.file.version.indentifier" },
]);

Another Solution

Katsuyama

Answer 6

I also would like rename a property in array: and I used thaht

db.getCollection('YourCollectionName').find({}).snapshot().forEach(function(a){
    a.Array1.forEach(function(b){
        b.Array2.forEach(function(c){
            c.NewPropertyName = c.OldPropertyName;
            delete c["OldPropertyName"];                   
        });
    });
    db.getCollection('YourCollectionName').save(a)  
});

Unclean

snapshot is deprecated as of MongoDB 4.0. – Dora

Also you could add if(b.hasOwnProperty("xxx")) for verifying that property or array exists. – Adelladella

Answer 7

The easiest and shortest solution using aggregate (Mongo 4.0+).

db.myCollection.aggregate([
  {
    $addFields: {
      "myArray.newField": {$arrayElemAt: ["$myArray.oldField", 0] }
    }
  },
  {$project: { "myArray.oldField": false}},
  {$out: {db: "myDb", coll: "myCollection"}}
])

The problem using forEach loop as mention above is the very bad performance when the collection is huge.

Hasa

Answer 8

My proposal would be this one:

db.nrel.component.aggregate([
   { $unwind: "$general.files.file" },
   {
      $set: {
         "general.files.file.version.identifier": {
            $ifNull: ["$general.files.file.version.indentifier", "$general.files.file.version.identifier"]
         }
      }
   },
   { $unset: "general.files.file.version.indentifier" },
   { $set: { "general.files.file": ["$general.files.file"] } },
   { $out: "nrel.component" } // carefully - it replaces entire collection.
])

However, this works only when array general.files.file has a single document only. Most likely this will not always be the case, then you can use this one:

db.nrel.componen.aggregate([
   { $unwind: "$general.files.file" },
   {
      $set: {
         "general.files.file.version.identifier": {
            $ifNull: ["$general.files.file.version.indentifier", "$general.files.file.version.identifier"]
         }
      }
   },
   { $unset: "general.files.file.version.indentifier" },
   { $group: { _id: "$_id", general_new: { $addToSet: "$general.files.file" } } },
   { $set: { "general.files.file": "$general_new" } },
   { $unset: "general_new" },
   { $out: "nrel.component" } // carefully - it replaces entire collection.
])

Mealymouthed

too complicated for such a simple task – Ramtil