Mongo error when using aggregation: sort exceeded memory limit

By default aggregation in MongoDB occurs in memory and pipeline stages have limit of 100 Mb RAM. Looks like you have exceeded this threshold. To handle large dataset you should enable aggregation pipeline stages to write data to temporary files. Use allowDiskUse option for that:

db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.aggregate([
    { "$project" : { "visitor_localdate" : 1 , "_id" : 0}},
    { "$sort" : { "visitor_localdate" : -1}}
], { "allowDiskUse" : true })

In case you are using aggregate queries.Put an index on the field by which you are sorting and then use sort operator.

Note: Place the sort operator at the beginning of the pipeline or before the $project, $unwind, and $group aggregation operators. If $project, $unwind, or $group occur prior to the $sort operation, $sort cannot use any indexes.

https://docs.mongodb.com/manual/reference/operator/aggregation/sort

Use { allowDiskUse: true } just after aggregation pipeline, like below:

db.collectionOrView.aggregate([], { allowDiskUse: true });

In my scenerio, I fixed it by adding an index for the sorted column

You don't need aggregation for this at all. Use the query

db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.find({}, { "_id" : 0, "visitor_localdate" : 1 }).sort({ "visitor_localdate" : -1 })

and put an index on visitor_localdate. This is simpler and faster than aggregation.

To fix it, enable the allowDiskUse option in your query :

The solution

Reference: Memory Restrictions

In MongoDB, the maximum memory limit for in-sort is 100M, and if you perform a larger sort, you need to use the allowDiskUse option to write the data to a temporary file to sort.

Add the allowDiskUse option to the query:

db.bigdata.aggregate(
[
 {$group : {_id : "$range", total : { $sum : 1 }}},
 {$sort : {total : -1}}
],
 {allowDiskUse: true}
);

For Mongoose

await Model.aggregate([{ $match: { foo: 'bar' } }]).allowDiskUse(true);

from https://mongoosejs.com/docs/api.html#query_Query-allowDiskUse

For those looking for an answer for pymongo

and obtain AttributeError: 'dict' object has no attribute '_txn_read_preference'

This works:

db.coll.aggregate([], allowDiskUse=True)

For me worked a combination of factors:

1. As already have been told I used { allowDiskUse: true } for my typeorm aggregation

2. I had to put { $sort: {} } before the aggregation.

   [{ $sort: {} }, ...aggregation]

And finally it worked!