Swap the values in a MongoDB array

Question

I have an array like "geo": [39.897867,-121.8909978] in my MongoDb collection.

What I need to do is to swap the values present inside the array.

Please help me on this. Thank you...

Meaning what exactly? We get it you have `lat` and `long` in the wrong order. But how are you meaning to fix? How many records. Development working or Production? All important in your question. Please edit. It helps us and you. — Neil Lunn, Mar 11 '14 at 13:43
You are correct. I need to change the lat, long order. I have to do in both development and production. I am having around 4L records. — Prakki, Mar 12 '14 at 04:58
Well there are some options for dealing with large updates. I've placed them as an answer. Give those some consideration and see what best fits your needs. — Neil Lunn, Mar 12 '14 at 05:59

score 11 · Accepted Answer · answered Mar 11 '14 at 13:54

11

You can swap by using array index . First find it and swap it.

db.myCollection.find().forEach(function(doc){
        db.myCollection.update({_id: doc._id},
          {$set: {"geo.0": doc.geo[1], "geo.1": doc.geo[0]}})
    })

answered Mar 11 '14 at 13:54

Sumeet Kumar Yadav

11,912
6
43
80

score 0 · Answer 2 · answered Mar 12 '14 at 05:48

Okay so since you do not seem to be struggling with the obvious iterative loop and you are worried about the number of items then let's look at your options:

mapReduce could be and option to do this. But be warned that your document format would not be the same. So this would be a way to copy the collection safely if it were possible for you to rename the used collection in your application. But this would not be an option if your collection is being updated regularly.

db.collection.mapReduce(
    function() {

        var t = this.geo[0];
        this.geo[0] = this.geo[1];        
        this.geo[1] = t;

        emit( this._id, this );
    },
    function(){},
    {
        "out" : { "replace": "newcollection" }
    }
)

Then you could safely run over this with db.eval() and a loop to "remodel" back to the original form:

db.eval(function(){
    db.newcollection.find().forEach(function(doc) {
        var newDoc = {};

        Object.keys(doc.value).forEach(function(key) {
            newDoc["key"] = doc.value["key"];
        });
        db.newcollection.update(doc._id,newDoc); 
    });
})

That gives you a "fixed" collection with another name. You may be able to just point your application to that, but if you cannot then you can use renameCollection to do the job:

db.collection.drop();
db.newcollection.rename("collection");

But there will be a lot of disk thrashing over the whole process, and of course you will need to re-create any indexes for the collection in any case.

Also you could go straight to db.eval() on the existing collection:

db.eval(function(){
    db.collection.find().forEach(function(doc){
        db.collection.update(
          {_id: doc._id},
          {"$set": {"geo.0": doc.geo[1], "geo.1": doc.geo[0]}}
        );
    });
})

But please read the documentation on this. You are locking your database at any rate when you are doing this. Also look at the FAQ on concurrency.

Of course the safest thing to do is just use that find and update loop from a client, as long as that client is as close as possible to the database server in networking terms, then it will be a lot of wire traffic, but it will be fairly localized.

At the end of the day, if the safest way is not a possibility (and possibly due to having your database with an external hosting provider), then you are stuck with the other options for updating on the server, or just ultimately wear the over the wire traffic.

But at least you have some options to consider.

Swap the values in a MongoDB array

2 Answers2

Linked