Compare two date fields in MongoDB

Question

in my collection each document has 2 dates, modified and sync. I would like to find those which modified > sync, or sync does not exist.

I tried

{'modified': { $gt : 'sync' }}

but it's not showing what I expected. Any ideas?

Thanks

Answer 1

You can not compare a field with the value of another field with the normal query matching. However, you can do this with the aggregation framework:

db.so.aggregate( [
    { $match: …your normal other query… },
    { $match: { $eq: [ '$modified', '$sync' ] } }
] );

I put …your normal other query… in there as you can make that bit use the index. So if you want to do this for only documents where the name field is charles you can do:

db.so.ensureIndex( { name: 1 } );
db.so.aggregate( [
    { $match: { name: 'charles' } },
    { $project: { 
        modified: 1, 
        sync: 1,
        name: 1,
        eq: { $cond: [ { $gt: [ '$modified', '$sync' ] }, 1, 0 ] } 
    } },
    { $match: { eq: 1 } }
] );

With the input:

{ "_id" : ObjectId("520276459bf0f0f3a6e4589c"), "modified" : 73845345, "sync" : 73234 }
{ "_id" : ObjectId("5202764f9bf0f0f3a6e4589d"), "modified" : 4, "sync" : 4 }
{ "_id" : ObjectId("5202765b9bf0f0f3a6e4589e"), "modified" : 4, "sync" : 4, "name" : "charles" }
{ "_id" : ObjectId("5202765e9bf0f0f3a6e4589f"), "modified" : 4, "sync" : 45, "name" : "charles" }
{ "_id" : ObjectId("520276949bf0f0f3a6e458a1"), "modified" : 46, "sync" : 45, "name" : "charles" }

This returns:

{
    "result" : [
        {
            "_id" : ObjectId("520276949bf0f0f3a6e458a1"),
            "modified" : 46,
            "sync" : 45,
            "name" : "charles",
            "eq" : 1
        }
    ],
    "ok" : 1
}

If you want any more fields, you need to add them in the $project.