update matched records from another collection?

Question

Can someone please help me update a collection based on another? I have a pickups collection like so.

{
    "_id": {
        "$oid": "53a46be700b94521574b6f75"
    },
    "created": {
        "$date": 1403236800000
    },
    "receivers": [
        {
            "model": "somemodel1",
            "serial": "someserial1",
            "access": "someaccess1"
        },
        {
            "model": "somemodel2",
            "serial": "someserial2",
            "access": "someaccess2"
        },
        {
            "model": "somemodel3",
            "serial": "someserial3",
            "access": "someaccess3"
        }
    ],
    "__v": 0
}

I would like to iterate through the receivers array and search each access in another collection and if found add the activity it was found in.

Here is the workorders collection I want to search in.

{
    "_id": {
        "$oid": "53af72481b2aeade0b46d025"
    },
    "activityNumber": "someactivity",
    "date": "06/28/2014",
    "lines": [
        {
            "Line #": "1",
            "Access Card #": "someaccess1"
        },
        {
             "Line #": "2",
            "Access Card #": "someaccess2"
        },
        {
             "Line #": "3",
            "Access Card #": "someacess3"
        }
    ],
}

And this is what I would like to end up with.

{
        "_id": {
            "$oid": "53a46be700b94521574b6f75"
        },
        "created": {
            "$date": 1403236800000
        },
        "receivers": [
            {
                "model": "somemodel1",
                "serial": "someserial1",
                "access": "someaccess1",
                "activityNumber": "someactivity"
            },
            {
                "model": "somemodel2",
                "serial": "someserial2",
                "access": "someaccess2",
                "activityNumber": "someactivity"
            },
            {
                "model": "somemodel3",
                "serial": "someserial3",
                "access": "someaccess3",
                "activityNumber": "someactivity"
            }
        ],
        "__v": 0
    }

I have created an array containing all the access from pickups.

var prodValues = db.pickups.aggregate([

    { "$unwind":"$receivers" },

    { "$group": {
        "_id": null,
        "products": { "$addToSet": "$receivers.access"}
    }}
])

I can easily iterate through the array and search the workorders colleciton and return the activity these are used in. But I'm not sure how to perform a find and update the pickups collection when found.

db.workorders.find({ "lines.Access Card #": { "$in": prodValues.result[0].products }},{activityNumber:1})

Thank you for your help.

Answer 1

Really I would loop this in the completely opposite order as that should be more efficient:

var result = db.workorders.aggregate([
    { "$project": { 
        "activityNumber": 1,
        "access": "$lines.Access Card #",
    }}
]).result;

result.forEach(function(res) {

    res.access.forEach(function(acc) {
        db.pickups.update(
            { "receivers.access": acc },
            { "$set": { "receivers.$.activityNumber": res.activityNumber } }
        );
    });

});

With MongDB 2.6 you can clean this up a bit with a cursor on the aggregate output and the use of the bulk operations API:

var batch = db.pickups.initializeOrderedBulkOp();
var counter = 0;

db.workorders.aggregate([
    { "$project": { 
        "activityNumber": 1,
        "access": "$lines.Access Card #",
    }}
]).forEach(function(res) {
    res.access.forEach(function(acc) {
        batch.find({ "receivers.access": acc }).updateOne(
            { "$set": { "receivers.$.activityNumber": res.activityNumber } }
        );
    });

    if ( counter % 500 == 0 ) {
        batch.execute();
        var batch = db.pickups.initializeOrderedBulkOp();
        counter = 0;
    }

});

if ( counter > 0 )
    batch.execute();

Either way, you are basically matching the document and the position of the array on the values of "access" returned from the first aggregation query, and in the current line. This allows the update of the related information at the specified position.

The MongoDB 2.6 improvements are that you are not pulling all the results out of the "workoders" collection into memory as an array, so only each document is pulled in from the the cursor results.

The Bulk operations actions store the "updates" in manageable blocks that should fall under the 16MB BSON limit and then you send this in those blocks instead of individual updates. The driver implementation should handle most of this, but there is some "self management" added in just to be safe.