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.

@Alexander Azarov, any ideas on fixing this? i've heard of people copying to fields in which $rename can go... — Andrew Samuelsen
– Andrew Samuelsen, Commented Feb 3, 2012 at 5:01
Personally I am writing scripts walking through the collection and doing migrations — Alexander Azarov
– Alexander Azarov, Commented Feb 3, 2012 at 5:15
If you are looking to do this with database commands: How to rename a field inside an array with database commands? — leonheess
– leonheess, Commented Feb 19, 2021 at 9:24

turivishal · Accepted Answer · 2021-04-28 14:35:03Z

54

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.

edited Apr 28, 2021 at 14:35

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

answered Sep 26, 2014 at 2:00

Eli Gassert

9,7713 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sander Toonen Over a year ago

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!

Faliorn Over a year ago

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

Patrycja K Over a year ago

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

Eli Gassert Over a year ago

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

NoamG Over a year ago

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

|

Cardinal System · Accepted Answer · 2023-08-21 12:09:20Z

27

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.

edited Aug 21, 2023 at 12:09

Cardinal System

3,5854 gold badges28 silver badges52 bronze badges

answered Feb 3, 2012 at 10:54

Remon van Vliet

18.6k3 gold badges54 silver badges57 bronze badges

3 Comments

Shannon Over a year ago

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

Remon van Vliet Over a year ago

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

Amit Beckenstein Over a year ago

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

Alexander Vogt · Accepted Answer · 2015-01-20 18:18:29Z

21

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

I exported the collection to json:

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

I did a find and replace on the json using my favoured text editing utility.
I reimported the edited file, dropping the old collection along the way:

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

edited Jan 20, 2015 at 18:18

Alexander Vogt

18.2k13 gold badges56 silver badges72 bronze badges

answered Jan 20, 2015 at 17:49

$fraccy's user avatar$

fraccy

2512 silver badges3 bronze badges

3 Comments

Matthew Bonig Over a year ago

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 =-}

Eli Gassert Over a year ago

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

Valerie R. Coffman Over a year ago

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.

Pete B · Accepted Answer · 2019-11-18 10:21:36Z

20

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).

edited Nov 18, 2019 at 10:21

Pete B

1,72718 silver badges12 bronze badges

answered Jun 25, 2019 at 20:21

Xavier Guihot

62.8k26 gold badges320 silver badges202 bronze badges

2 Comments

Haidang Nguyen Over a year ago

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

Amit Beckenstein Over a year ago

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 */] }.

Sudhesh Gnanasekaran · Accepted Answer · 2022-05-12 03:19:31Z

15

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

edited May 12, 2022 at 3:19

answered Sep 9, 2021 at 3:10

Sudhesh Gnanasekaran

4113 silver badges13 bronze badges

Comments

Cedric Michel · Accepted Answer · 2018-08-22 09:07:01Z

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)  
});

edited Aug 22, 2018 at 9:07

answered Aug 22, 2018 at 7:34

Cedric Michel

5205 silver badges13 bronze badges

2 Comments

Dan Dascalescu Over a year ago

snapshot is deprecated as of MongoDB 4.0.

MonkeyDLuffy Over a year ago

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

loonis · Accepted Answer · 2021-08-31 09:29:22Z

4

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.

answered Aug 31, 2021 at 9:29

loonis

1,51718 silver badges21 bronze badges

Comments

Wernfried Domscheit · Accepted Answer · 2020-02-03 12:55:31Z

1

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.
])

answered Feb 3, 2020 at 12:55

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

1 Comment

BabyishTank Over a year ago

too complicated for such a simple task

Collectives™ on Stack Overflow

MongoDB rename database field within array

8 Answers 8

8 Comments

3 Comments

3 Comments

2 Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

8 Comments

3 Comments

3 Comments

2 Comments

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related