Aggregation Group by Count ($sum) maxed at 100k count

Working with Data
1

i am trying to perform a “count” of a "$group. It all works fine if the total count items are less than 100k items.

When its beyound 100k items, it will return results, but wrong results because if you total them manually, it will be sumed to 100k items.

Is there any way around this?

Thanks

2

Hi @zhen_shin_kong welcome to the community!

When its beyound 100k items, it will return results, but wrong results because if you total them manually, it will be sumed to 100k items.

I tried a quick repro in MongoDB 6.0.1 using 200,000 documents and don’t see this issue:

> db.test.find()
[
  { _id: 1 },  { _id: 2 },
  { _id: 3 },  { _id: 4 },
  { _id: 5 },  { _id: 6 },
  { _id: 7 },  { _id: 8 },
  { _id: 9 },  { _id: 10 },
 ...
  {_id: 200000}

> db.test.aggregate([ {$group:{_id:null, sum:{$sum:'$_id'}, count:{$sum:1}}} ])
[ { _id: null, sum: Long("20000100000"), count: 200000 } ]

It appears to correctly sums all the numbers together, and also indicates that there are 200,000 documents in the collection.

Could you post a small self-contained code that reproduces this, and also post:

  • Your full MongoDB version (e.g. output of db.version())
  • What error are you seeing if any
  • What’s the output of db.collection.stats() of the collection in question

Best regards
Kevin

2 Likes
3

Here, i am matching 1 weeks worth of data. There is 800k entry.

image
image865×403 6.33 KB

4

If i execute the same query in mongo-client. its fine.

image
image709×693 8.7 KB

5

When i execute it via Mongo Atlas Web UI. there is where i see the limit.

image
image780×553 24.3 KB

6

Running version.
image

My appolgize, i can only post 1 uploads per reply (so have to split the response)

7

An _id when it is an ObjectId is not a number so you cannot $sum. If you want to count using sum you need to use

sum : { $sum : 1 }

Next time you publish code sample please post it using text format rather than a image. This way we can cut-n-paste into our system for experimenting and cut-n-paste into our replies.

1 Like
8

I believe this is expected, since the Atlas aggregation pipeline builder is primarily designed for building pipelines, rather than executing them. That is, the Atlas pipeline builder allows you to easily plan & design your aggregation, and once you finished designing them, you should be exporting the finished pipeline to be used in a driver. The pipeline builder provides an easy way to export your pipeline.

In fact, the aggregation pipeline builder you see in Atlas is the same one that you see in MongoDB Compass. However the pipeline builder in Compass has a configurable limit which defaults to 100,000 documents (the same limit as you observed in Atlas). From the description of this setting in Compass:

When Sample Mode is enabled, specifies the number of documents passed to $group, $bucket, and $bucketAuto stages. Lower limits improve pipeline running time, but may miss documents.

But while this limit is configurable in Compass, presently I don’t think it is configurable in Atlas.

Best regards
Kevin

3 Likes
9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.