0

I have been able to create an aggregation query to count by a field of my data object.

db.getCollection.aggregate([
{"$group" : {"_id": "$attr", "count": {"$sum" : 1}}},
{"$sort": {"count": -1}}
])

which results in a {_id,count} based documents sorted in descending order of count. All good until now.

What I am trying next is to filter in (match) only those documents which have count above N (say 100) threshold. But when I execute:

db.getCollection.aggregate([
{"$group" : {"_id": "$attr", "count": {"$sum" : 1}}},
{"$sort": {"count": -1}},
{"$match": {"count": {$gte:["count",100]}}}
])

the query returns empty results. What is the way to achieve this? I have also tried usinng $filter instead of $match to no success.

Naman
  • 23,555
  • 22
  • 173
  • 290
  • Please share collection at jsoneditor – Mahesh Bhatnagar Nov 14 '19 at 09:25
  • @MaheshBhatnagar shouldn't really be required in my opinion, but for the sake of testing, assume `[{"attr": "a"},{"attr": "b"},{"attr": "a"},{"attr": "c"}]` and now the result of my final query(with threshold `2`) should be something like `[{"_id": "a","count":2}]`. – Naman Nov 14 '19 at 09:29
  • {"$match": {$gte:["$count",100]}} must do the job – matthPen Nov 14 '19 at 09:31
  • 1
    Note that if you actually have some prior experience with SQL, there is [SQL to Aggregation Mapping Chart](https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/) which has many common examples, include a "HAVING" clause, which is essentially the question you are asking. – Neil Lunn Nov 14 '19 at 10:10

1 Answers1

1

Please check your match condition. Sort condition after use match condition is wrong.

db.getCollection.aggregate([
{"$group" : {"_id": "$usage", "count": {"$sum" : 1}}},
{"$sort": {"count": -1}},
{"$match": {"count":{ $gte: 2 } }},
])
Mahesh Bhatnagar
  • 877
  • 1
  • 4
  • 7
  • I feel I must note that this account has posted several answers recently which are basically lifted from existing answers. Finding existing answers on StackOverflow and then posting them as your own is not how this is done. Answers which basically consist of "Try this" and a block of code with no explanation, generally present as something obtained from another source rather than the direct knowledge of the person posting the answer. **Note this is the second time I have posted this same notice** – Neil Lunn Nov 14 '19 at 10:11
  • @Neil Lunn How can say that i lifted code from existing answers. – Mahesh Bhatnagar Nov 14 '19 at 10:15
  • @Neil Lunn you are right that i does not demonstrate but code is not copied. I am trying to give demonstrate with code – Mahesh Bhatnagar Nov 14 '19 at 10:37
  • Code is easily copied. And to be frank, the very few words you used in the answer actually form a completely incorrect statement ( along with the code, which contains unnecessary statements as well ). The plain fact is you made a "correction" to the code in the question, which is very easily obtainable from finding existing answers online ( though clearly the person asking the question did not manage to find this themselves ) but your explanation of the correction does not completely match what was actually done. A good answer does have the ability to **explain** why something is done. – Neil Lunn Nov 14 '19 at 10:42
  • I mean, in all honesty ( and I'm really not doing this as self promotional ) but the few words I placed in the comment on the question itself ( and the external link to documentation ) do indeed make a much clearer explanation to anyone who might be *trying* MongoDB after some exposure to a SQL RDBMS. And the reason I say that it that is probably going to be a lot of people. So indeed, the existing answer from many years ago is a lot more informative and accurate. No need to change that. If you think you can, then you are welcome to add your own detailed response to that existing question. – Neil Lunn Nov 14 '19 at 10:47
  • Next i will try to explain the answer. – Mahesh Bhatnagar Nov 14 '19 at 10:58
  • I think the overall point here is that if you could, then you would have already, and particularly given the point that I have highlighted you "appear to be copying content from existing answers" on previous occasions. Nonetheless, if you believe you can then go ahead and do so. But as I did suggest, your time is probably better spent adding a **better** explanation and answer to the [already existing question](https://stackoverflow.com/questions/5550253/what-is-the-correct-way-to-do-a-having-in-a-mongodb-group-by) than answering questions here which have already been asked & answered – Neil Lunn Nov 14 '19 at 11:06