MongoDB $set in aggregate [duplicate]

Question

I want to set the value of a new field in every document of a "destination" collection, based on the value of a field in documents of another "source" collection.

Documents of the destination collection look like this:

db.names:

{ 
    "name" : "Larry", 
    "info" : {
        "phone" : "5551212"
    }
}
{ 
    "name" : "Curly", 
    "info" : {
        "phone" : "5551213"
    }
}
{ 
    "name" : "Moe", 
    "info" : {
        "phone" : "5551234"
    }
}

Documents of the second collection look like this (I want this timezone to be added to the destination collection):

db.phones:

{ 
    "phone" : "5551212", 
    "timezone" : "UTC-6" 
}

{ 
    "phone" : "5551213", 
    "timezone" : "UTC-7" 
}

{ 
    "phone" : "5551234", 
    "timezone" : "UTC-6" 
}

I want the documents of the first, "destination", collection to end up looking like this:

db.names:

{ 
    "name" : "Larry", 
    "info" : {
        "phone" : "5551212",
        "timezone" : "UTC-6"
    }
}
{ 
    "name" : "Curly", 
    "info" : {
        "phone" : "5551213",
        "timezone" : "UTC-7"
    }
}
{ 
    "name" : "Moe", 
    "info" : {
        "phone" : "5551234",
        "timezone" : "UTC-6"
    }
}

In other words, I have a very large collection (phones) that includes timezones and a very large collection(names) that does not, and I want the first collection to include those timezones, using the phone number in both as the key.

I have tried this in mongoShell with no luck:

list = db.names.aggregate([
    { $match: { } },

    { $lookup: {  
                from: "phones",
                localField: "info.phone",
                foreignField: "phone",
                as: "zoneinfo"
            }
    }
]);

list.result.forEach(function(x) {
    db.names.update({_id:x._id}, {$set:{'info.timezone':'zoneinfo.timezone'}});
});

So, link the collection of timezones and add it to list, the result, as a new field on each document (this much works). Then, since we can't do an update in an aggregate, iterate over the resulting documents, adding a new permanent field, info.timezone, from the 'temporary' one, zoneinfo.timezone, added in the previous operation.

What am I doing wrong here? Is there some other approach which would be preferable? There are thousands of documents in each collection, so hand work is not desirable.

Answer 1

One thing you need to keep in mind is that $lookup returns zoneInfo as an array so in order to use that field you need to run $unwind on it. Then you can simply reshape your document using $addFields and $project. Try:

db.names.aggregate([
    {
        $lookup: {
            from: "phones",
            localField: "info.phone",
            foreignField: "phone",
            as: "phoneDetails"
        }
    },
    {
        $unwind: "$phoneDetails"
    },
    {
        $addFields: {
            "info.timezone": "$phoneDetails.timezone"
        }
    },
    {
        $project: {
            phoneDetails: 0
        }
    }
])

And in the last step you can add $out if you want to update existing collection.