CODEWITHSUNDEEP
mongodbaggregation-framework
user22197
user22197

Reputation: 129

mongodb date range query to fetch records that have startDate & endDate

I am relatively new to mongodb. we have documents that have startDate and endDate in a collection. Whats the best way to query only those documents that startDate and endDate comes under another date range.

Lets say we have following sub documents in reports array:

{reports: [
 {
  "startDate" : ISODate("2017-07-02T00:00:00Z"),
  "endDate" : ISODate("2017-07-08T00:00:00Z"),
  "data" : [ ] 
 },
 {
  "startDate" : ISODate("2017-07-09T00:00:00Z"),
  "endDate" : ISODate("2017-07-15T00:00:00Z"),
  "data" : [ ] },
 {
  "startDate" : ISODate("2017-07-16T00:00:00Z"),
  "endDate" : ISODate("2017-07-22T00:00:00Z"),
  "data" : [ ] }
]}

Now I want to query only subdocuments in the reports array between 2017-07-01 and 2017-07-16. Expected sub documents in results should be:

[{
 "startDate" : ISODate("2017-07-02T00:00:00Z"),
 "endDate" : ISODate("2017-07-08T00:00:00Z"),
 "data" : [ ] 
},
{
 "startDate" : ISODate("2017-07-09T00:00:00Z"),
 "endDate" : ISODate("2017-07-15T00:00:00Z"),
 "data" : [ ] }]

I have done the aggregation and projection part of the query. and it seems to be working correct. The only problem is what condition should I use in the filter. here is the filter part of the query that Iam trying to use but not giving me expected results:

{$filter: {
  input: "$reports", as: "reports", 
  cond: {$and: 
   [{ $gte: ["$$reports.startDate", new Date("2017-07-02") ]}, 
    { $lte: ["$$reports.endDate", new Date("2017-07-08") 
]}]}}}

Upvotes: 0

Views: 898

Answers (1)

Skylar Graika
Skylar Graika

Reputation: 79

Your solution is close. 

db.DailyReports.aggregate([{
  $project: {
    reports: {
      $filter: {
        input: '$reports',
        as: 'report',
        cond: {
          $and: [
           { $gte: [ '$$report.startDate', new ISODate('2017-07-02') ] },
           { $lte: [ '$$report.endDate', new ISODate('2017-07-08') ] }
          ]
        }
      }
    }
  }
}])

Upvotes: 1

Related Questions