Mongodb aggregation for date comparison

Question

I have a collection that contains documents in the following format.

{
    "_id" : ObjectId("58d9fa5cb35630097b96acab"),
    "invoice_id" : ObjectId("58d9fa5cb35630097b96acaa"),
    "amount" : 717.6,
    "date" : "2017-03-28",
    "by_id" : ObjectId("58d9d6a0b35630097b96a729")

},
{
    "_id" : ObjectId("58d9ff77b35630097b96acb8"),
    "invoice_id" : ObjectId("58d9ff77b35630097b96acb7"),
    "amount" : 717.6,
    "date" : "2017-03-28",
    "by_id" : ObjectId("58d9d6a0b35630097b96a729")
}

I need to make a date comparison using aggregation, but dates in the documents are not in the correct format. So I'm trying to add a new field called c_date with correctly formatted date value, using $addFields function for each document.

db.table.aggregate([
{ $addFields: { c_date : new ISODate('2017-03-29')} }
])

The above query results

{
    "_id" : ObjectId("58d9fa5cb35630097b96acab"),
    "invoice_id" : ObjectId("58d9fa5cb35630097b96acaa"),
    "amount" : 717.6,
    "date" : "2017-03-28",
    "by_id" : ObjectId("58d9d6a0b35630097b96a729"),
    "c_date": 2017-03-29 00:00:00.000Z
},
{
    "_id" : ObjectId("58d9ff77b35630097b96acb8"),
    "invoice_id" : ObjectId("58d9ff77b35630097b96acb7"),
    "amount" : 717.6,
    "date" : "2017-03-28",
    "by_id" : ObjectId("58d9d6a0b35630097b96a729")
    "c_date": 2017-03-29 00:00:00.000Z
}

I want to add the formatted value of date field to the c_date field in the each document. How to achieve it?

I think it is still not possible to reference the document itself in an update. You have to do it programmatically... However, what kind of date comparison do you need to do? Maybe your format will do it (for example, string comparison "2017-03-28">"2017-03-01" is true). — dgiugg
So simple string comparison should work, if the format is perfectly respected (always "02" or "08" for days and months, and not "2" or "8"). Otherwise, if you still want to convert it to a real date format (which is best practice), you have to do it programmatically looping on your entire collection, for example with a forEach. — dgiugg

Clement Amarnath Clement Amarnath · Accepted Answer · 2017-03-30T15:11:33

My approach to update the c_date with formatted date field from the same document is as shown below

db.table.find().forEach(function(element){
 element.c_date = new Date(element.date);
 db.table.save(element);
});

Initial records

{
        "_id" : ObjectId("58d9fa5cb35630097b96acab"),
        "invoice_id" : ObjectId("58d9fa5cb35630097b96acaa"),
        "amount" : 717.6,
        "date" : "2017-03-28",
        "by_id" : ObjectId("58d9d6a0b35630097b96a729"),
        "c_date" : ISODate("2017-03-29T00:00:00Z")
}
{
        "_id" : ObjectId("58d9ff77b35630097b96acb8"),
        "invoice_id" : ObjectId("58d9ff77b35630097b96acb7"),
        "amount" : 717.6,
        "date" : "2017-03-28",
        "by_id" : ObjectId("58d9d6a0b35630097b96a729"),
        "c_date" : ISODate("2017-03-29T00:00:00Z")
}

After execution of the function

{
        "_id" : ObjectId("58d9fa5cb35630097b96acab"),
        "invoice_id" : ObjectId("58d9fa5cb35630097b96acaa"),
        "amount" : 717.6,
        "date" : "2017-03-28",
        "by_id" : ObjectId("58d9d6a0b35630097b96a729"),
        "c_date" : ISODate("2017-03-28T00:00:00Z")
}
{
        "_id" : ObjectId("58d9ff77b35630097b96acb8"),
        "invoice_id" : ObjectId("58d9ff77b35630097b96acb7"),
        "amount" : 717.6,
        "date" : "2017-03-28",
        "by_id" : ObjectId("58d9d6a0b35630097b96a729"),
        "c_date" : ISODate("2017-03-28T00:00:00Z")
}

Using $project you can add new fields or project the existing fields in the documents to next stage of pipeline, but modify/update a field is on same document is not possible.

Whereas we have a workaround and $out, which will takes the documents returned by the aggregation pipeline and writes them to a specified collection.

Hope it Helps!

Mongodb aggregation for date comparison

1 Answers