CODEWITHSUNDEEP
mongodbaggregation-framework
gprime
gprime

Reputation: 2353

Mongo Aggregate $and in $match Not Returning Results

I am having trouble trying to figure out why this aggregate is not returning results.

I am trying to find duplicates on a certain date. When I do a find I can see that multiple results are returned:

db.getCollection('transfers').find({createdAt: { $gte: ISODate('2018-07-09') }, dupField:'123456'})

Returns 4 results.

But when I try an aggregate it never returns results.

var g = db.transfers.aggregate([
  { $group: { _id: { dupField: "$dupField" }, count: { $sum: 1 }}},
  { $match: { 
      $and: [
          { "count": { $gt: 1} },
          { "createdAt": { $gte: ISODate('2018-07-09') } }
      ]
  }},
  { $sort: { "count": -1 }}
])
print(g)

The aggregate works when I do not use the createdAt in the $match but when I add the createdAt in the $match it does not work.

I am not sure what I am doing wrong. Any help would be appreciated.

Upvotes: 2

Views: 2420

Answers (1)

Anurag Wagh
Anurag Wagh

Reputation: 1086

Mongo's aggregation framework consists of stages and each stage transforms the document as it passes through the pipeline.

In your case, there are two stages

  1. the first stage of your pipeline is to group the document.

    { $group: { _id: { dupField: "$dupField" }, count: { $sum: 1 }}}

  2. then you pass over this groups to the second stage of your pipeline where you match createdAt and count.

But that won't work cause after stage 1 your document will get transformed and look
like this

{ _id : 123456, // your dupField count:987 } at second stage you don't have access to createdAt.

If you want to filter it based on { "createdAt": { $gte: ISODate('2018-07-09') } } move it to the stage one i.e. before you perform the $group phase of the pipeline

Which would look something like this. 

var g = db.transfers.aggregate([

{ $match: { "createdAt": { $gte: ISODate('2018-07-09') } } }, 

{ $group: { _id: { dupField: "$dupField" }, count: { $sum: 1 }}},

{ $match: {  "count": { $gt: 1} } },

{ $sort: { "count": -1 }}
])
print(g)

https://docs.mongodb.com/manual/core/aggregation-pipeline/

Upvotes: 3

Related Questions