MongoDB query for finding schemes with more than a year?

Question

Given the following schema, I want to write a MongoDB scheme to find all schemes with duration of more than a year. ie (start - end) > 1 year. I am not sure if I can specify such an expression in mongodb query (start - end) > 1 year.

{
    "update" : ISODate("2017-09-26T15:22:13.172Z"),
    "create" : ISODate("2017-09-26T15:22:13.172Z"),
    "scheme" : {
        "since" : ISODate("2017-09-26T15:22:13.172Z"),
        "startDate":  ISODate("2017-09-26T15:22:13.172Z"),
        "endDate":  ISODate("2018-09-26T15:22:13.172Z"),
    },
}

Possible duplicate of [MongoDb query condition on comparing 2 fields](https://stackoverflow.com/questions/4442453/mongodb-query-condition-on-comparing-2-fields) — s7vr, Sep 29 '17 at 01:55

score 0 · Answer 1 · answered Sep 28 '17 at 17:09

You can use aggregation with subtract:

db.yourcollection.aggregate(
[{ 
    $project : { dateDifference: { $subtract: [ "$scheme.endDate", "$scheme.endDate" ] }},
    $match : { "$dateDifference" : { $gt :  31536000000 } } 
}]);

(* 31536000000 = milliseconds per year)

You may use another $project to output any fields you need in the matching documents.

MongoDB query for finding schemes with more than a year?

1 Answers1