CODEWITHSUNDEEP
mongodbmongoose
Gaurav
Gaurav

Reputation: 1554

MongoDB $match won't work with $group in an aggregate query

This is what my collection looks like.

{ "name" : "Lorem", "createdate" : ISODate("2018-09-12T04:02:41.208Z")},
{ "name" : "Impusom", "createdate" : ISODate("2018-09-12T04:02:41.208Z")}
{ "name" : "Lorem", "createdate" : ISODate("2018-11-01T04:02:41.208Z")}
{ "name" : "Damut", "createdate" : ISODate("2018-11-12T04:02:41.208Z")}
{ "name" : "Remetat", "createdate" : ISODate("2019-01-01T04:02:41.208Z")}

I want to extract distinct createdate values so I can mark them on a calendar widget. I wish to ignore time.

Here is the pipeline which is I am currently using.

db.mycollection.aggregate({
    '$project': {
      'y': {
        '$year': '$createdate'
      },
      'm': {
        '$month': '$createdate'
      },
      'd': {
        '$dayOfMonth': '$createdate'
      }
    }
  }, {
    '$group': {
      '_id': {
        'year': '$y',
        'month': '$m',
        'day': '$d'
      }
    }
  }, {
    '$sort': {
      '_id.year': 1,
      '_id.month': 1,
      '_id.day': 1
    },
  })

The result looks like this

{
    "_id" : {
        "year" : 2019,
        "month" : 1,
        "day" : 1
    }
},
{
    "_id" : {
        "year" : 2018,
        "month" : 11,
        "day" : 12
    }
},
{
    "_id" : {
        "year" : 2018,
        "month" : 11,
        "day" : 1
    }
},
{
    "_id" : {
        "year" : 2018,
        "month" : 9,
        "day" : 12
    }
}

I wish to improve the query and limit data to a particular year. e.g. 2018

Hence I tried adding a match condition before $project

{ '$match': { "createdate":{ "$gte": ISODate("2017-12-31T18:30:00.000Z"), "$lt": ISODate("2018-12-31T18:30:00.000Z") } }
},

The result is limited to year 2018 but then $group stops working hence I get duplicated records for dates.

I tried moving the $match option after $sort which allows $group to work but won't apply $match condition.

I would really appreciate a nudge in the right direction.

Update ---

So the above query with $match condition is working in mongoplayground.net But not with my mongoose code.

    const pipeline = [{
        "$match": {
          "createdate": {
            "$gte": moment().startOf('year'),
            "$lt": moment().endOf('year'),
          }
        }
      },{
        '$project': {
          'y': {
            '$year': '$createdate'
          },
          'm': {
            '$month': '$createdate'
          },
          'd': {
            '$dayOfMonth': '$createdate'
          }
        }
      }, {
        '$group': {
          '_id': {
            'year': '$y',
            'month': '$m',
            'day': '$d'
          }
        }
      }, {
        '$sort': {
          '_id.year': 1,
          '_id.month': 1,
          '_id.day': 1
        },
      }];


Collection.aggregate(pipeline, (err, dates) => { res.json({ data: dates });  });

The response returns empty array.

Upvotes: 0

Views: 182

Answers (1)

Akrion
Akrion

Reputation: 18515

Change your $match section to:

"$match": {
  "createdate": {
    "$gte": moment().startOf('year').toDate(),
    "$lt": moment().endOf('year').toDate(),
  }
}

And see if this helps. I do not think moment would work as a valid date parameter

Upvotes: 1

Related Questions