MongoDB: Update a field of an item in array with matching another field of that item

Question

I have a data structure like this:
We have some centers. A center has some switches. A switch has some ports.

    {
     "_id" : ObjectId("561ad881755a021904c00fb5"),
     "Name" : "center1",
     "Switches" : [
        {
            "Ports" : [
                {
                    "PortNumber" : 2,
                    "Status" : "Empty"
                },
                {
                    "PortNumber" : 5,
                    "Status" : "Used"
                },
                {
                    "PortNumber" : 7,
                    "Status" : "Used"
                }
            ]
        }
     ]
  }

All I want is to write an Update query to change the Status of the port that it's PortNumber is 5 to "Empty".
I can update it when I know the array index of the port (here array index is 1) with this query:

db.colection.update(
    // query
    {
        _id: ObjectId("561ad881755a021904c00fb5")
    },
    // update 
    {
        $set : { "Switches.0.Ports.1.Status" : "Empty" }
    }
);

But I don't know the array index of that Port.
Thanks for help.

Answer 1

You would normally do this using the positional operator $, as described in the answer to this question:

Update field in exact element array in MongoDB

Unfortunately, right now the positional operator only supports one array level deep of matching.

There is a JIRA ticket for the sort of behavior that you want: https://jira.mongodb.org/browse/SERVER-831

In case you can make Switches into an object instead, you could do something like this:

db.colection.update(
    {
        _id: ObjectId("561ad881755a021904c00fb5"),
        "Switch.Ports.PortNumber": 5
    }, 
    {
        $set: {
            "Switch.Ports.$.Status": "Empty"
        }
    }
)

Answer 2

Since you don't know the array index of the Port, I would suggest you dynamically create the $set conditions on the fly i.e. something which would help you get the indexes for the objects and then modify accordingly, then consider using MapReduce.

Currently this seems to be not possible using the aggregation framework. There is an unresolved open JIRA issue linked to it. However, a workaround is possible with MapReduce. The basic idea with MapReduce is that it uses JavaScript as its query language but this tends to be fairly slower than the aggregation framework and should not be used for real-time data analysis.

In your MapReduce operation, you need to define a couple of steps i.e. the mapping step (which maps an operation into every document in the collection, and the operation can either do nothing or emit some object with keys and projected values) and reducing step (which takes the list of emitted values and reduces it to a single element).

For the map step, you ideally would want to get for every document in the collection, the index for each Switches and Ports array fields and another key that contains the $set keys.

Your reduce step would be a function (which does nothing) simply defined as var reduce = function() {};

The final step in your MapReduce operation will then create a separate collection Switches that contains the emitted Switches array object along with a field with the $set conditions. This collection can be updated periodically when you run the MapReduce operation on the original collection. Altogether, this MapReduce method would look like:

var map = function(){
    for(var i = 0; i < this.Switches.length; i++){
        for(var j = 0; j < this.Switches[i].Ports.length; j++){
            emit( 
                {
                    "_id": this._id, 
                    "switch_index": i, 
                    "port_index": j 
                }, 
                {
                    "index": j, 
                    "Switches": this.Switches[i],  
                    "Port": this.Switches[i].Ports[j],                      
                    "update": {
                        "PortNumber": "Switches." + i.toString() + ".Ports." + j.toString() + ".PortNumber",
                        "Status": "Switches." + i.toString() + ".Ports." + j.toString() + ".Status"
                    }                    
                }
            );
        }            
    }
};

var reduce = function(){};

db.centers.mapReduce(
    map,
    reduce,
    {
        "out": {
            "replace": "switches"
        }
    }
);

Querying the output collection Switches from the MapReduce operation will typically give you the result:

db.switches.findOne()

Sample Output:

{
    "_id" : {
        "_id" : ObjectId("561ad881755a021904c00fb5"),
        "switch_index" : 0,
        "port_index" : 1
    },
    "value" : {
        "index" : 1,
        "Switches" : {
            "Ports" : [ 
                {
                    "PortNumber" : 2,
                    "Status" : "Empty"
                }, 
                {
                    "PortNumber" : 5,
                    "Status" : "Used"
                }, 
                {
                    "PortNumber" : 7,
                    "Status" : "Used"
                }
            ]
        },
        "Port" : {
            "PortNumber" : 5,
            "Status" : "Used"
        },
        "update" : {
            "PortNumber" : "Switches.0.Ports.1.PortNumber",
            "Status" : "Switches.0.Ports.1.Status"
        }
    }
}

You can then use the cursor from the db.switches.find() method to iterate over and update your collection accordingly:

var newStatus = "Empty";
var cur = db.switches.find({ "value.Port.PortNumber": 5 });     

// Iterate through results and update using the update query object set dynamically by using the array-index syntax.
while (cur.hasNext()) {
    var doc = cur.next();
    var update = { "$set": {} };
    // set the update query object
    update["$set"][doc.value.update.Status] = newStatus;

    db.centers.update(
        {
            "_id": doc._id._id, 
            "Switches.Ports.PortNumber": 5
        }, 
        update 
    );      
};