How to combine two collections in mongodb?

Question

I want to combine two collections ("messages" and "date"). The collection "messages" contains documents like this one:

{ 
    "_id" : ObjectId("4f16fc97d1e2d32371003e27"), 
    "body" : "the scrimmage is still up in the air...\n\n\nwebb said that they didnt want to scrimmage...\n\nthe aggies  are scrimmaging each other... (the aggie teams practiced on \nSunday)\n\nwhen I called the aggie captains to see if we could use their field.... they \nsaid that it was tooo smalll for us to use...\n\n\nsounds like bullshit to me... but what can we do....\n\n\nanyway... we will have to do another practice Wed. night....    and I dont' \nknow where we can practice.... any suggestions...\n\n\nalso,  we still need one  more person...", 
    "subFolder" : "notes_inbox", 
    "mailbox" : "bass-e", 
    "filename" : "450.", 
    "X-cc" : "", 
    "From" : "michael.simmons@enron.com", 
    "Subject" : "Re: Plays and other information", 
    "X-Folder" : "\\Eric_Bass_Dec2000\\Notes Folders\\Notes inbox", 
    "Content-Transfer-Encoding" : "7bit", 
    "X-bcc" : "", 
    "To" : "eric.bass@enron.com", 
    "X-Origin" : "Bass-E", 
    "X-FileName" : "ebass.nsf", 
    "X-From" : "Michael Simmons", 
    "Date" : "Tue, 14 Nov 2000 08:22:00 -0800 (PST)", 
    "X-To" : "Eric Bass", 
    "Message-ID" : "<6884142.1075854677416.JavaMail.evans@thyme>", 
    "Content-Type" : "text/plain; charset=us-ascii", 
    "Mime-Version" : "1.0", 
}

The collection "date" contains documents like this one:

{ 
    "_id" : ObjectId("4f16fc97d1e2d32371003e27"), 
    "year" : NumberInt(2000), 
    "month" : NumberInt(11), 
    "day" : NumberInt(14)
}

The day, the month and the year should be inserted into the collection messages. I have tried different approaches but have not come to any solution.

An example of what I've tried:

db.messages.aggregate([
    {
        $lookup:
        {
            from: "date",
            localField: "Date",
            foreignField: "year",
            as: "Year"

        }
    }
])

Answer 1

The problem as the user @Vanojx suggested in his comment,

you are matching "Tue, 14 Nov 2000 08:22:00 -0800 (PST)" with "2000"

However, why is the Date field in messages collection in RFC format and stored as a string? It should have been stored as a Date object in ISO format.

According to this SO post, aggregation framework doesn't operate in the v8 engine and you won't have JS functions to do manipulation on the string.

Besides manipulating the string to extract "2000" out of it, to be later used to join on the "date" collection doesn't sound like a right approach to me.

If possible please convert it from string to Date object. To convert date field to Date object you could do this,

//please make back up of your db just incase
db.messages.find({}).forEach(function (doc) {
    doc.Date = new Date(doc.Date);
    db.messages.save(doc);
});

Once you convert your "Date" field from string to a Date Object, following query should work for you.

db.messages.aggregate([
    {
        $project: {
            body: 1,
            subFolder: 1,
            Date: 1,
            //add other properties as you need
            //you could even do following which will give you all fields of the document
            //document: "$$ROOT"

            year: {$year: "$Date"}
        }        
    },
    {
        $lookup: {
            from: "date",
            localField: "year",
            foreignField: "year",
            as: "Year"
        }
    }
    ])

Here we first extract the year out of the Date field using $year. Then we will join using the $lookup on that field.

I hope this helps.