CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
dontmentionthebackup
dontmentionthebackup

Reputation: 2915

Is it possible to use a MongoDB aggregate query with date matching?

Im trying to get the count of certain items grouped on certain dates. This is working using the following aggregate query:

// this query works, without matching dates
[
  {'$match': {
    'some_id': ObjectId('foobar'),
    'some_boolean_value': true
    }
  },
  {'$project':
    {'day':
      {'$substr': ['$some_date', 0, 10]}}
  },
  {'$group': {_id: '$day', count: { '$sum': 1 }}},
  {'$sort': {_id: -1}}
]

The next step is that I want to use this query but with date limits. I want the count, grouped per day, between certain date limits.

 // the query below does not work as soon as date matching is added
 // this query always return 0 documents
[
  {'$match': {
    'some_id': ObjectId('foobar'),
    'some_boolean_value': true,
    'some_date':
      {
        '$gte': '2015-08-01T00:00:00.000Z',
        '$lte': '2015-08-31T23:59:59.999Z'
      }
    }
  },
  {'$project':
    {'day':
      {'$substr': ['$some_date', 0, 10]}}
  },
  {'$group': {_id: '$day', count: { '$sum': 1 }}},
  {'$sort': {_id: -1}}
]

Upvotes: 0

Views: 2393

Answers (1)

Mike Argyriou
Mike Argyriou

Reputation: 1310

You want to filter documents and match only those in a specified datetime window. But you use string comparison instead of date comparison.

Therefore replace this:

'$gte': '2015-08-01T00:00:00.000Z',
'$lte': '2015-08-31T23:59:59.999Z'

with this:

'$gte': new Date('2015-08-01T00:00:00.000Z'),
'$lte': new Date('2015-08-31T23:59:59.999Z')

Upvotes: 2

Related Questions