0

I have a mongodb collection with items prices by dates.

{ "_id" : { "item" : "eggs", "date" : "2018-03-02" }, "price" : 2.20 }
{ "_id" : { "item" : "meat", "date" : "2018-03-02" }, "price" : 12.12 }
{ "_id" : { "item" : "bread", "date" : "2018-03-02" }, "price" : 3.45 }
{ "_id" : { "item" : "eggs", "date" : "2018-03-03" }, "price" : 2.30 }
{ "_id" : { "item" : "bread", "date" : "2018-03-03" }, "price" : 3.35 }

Some items does not have price for several last dates.

I should collect last price of each item.

I created query:

db.getCollection('prices').aggregate(
[
    {  
        '$sort' : { "_id.date" : -1 }    
    }, {
        '$group' : { 
            '_id' : '$_id.item' ,
            'lastPrice' : {'$first' : '$price'}
            }
    }
])

But result was: Command failed with error 16819: 'Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

I do not want set allowDiskUse:true. That looks that mongo rearrange the total price collection on disk.

Does anybody know a solution without DiskUse ?

Oleg
  • 443
  • 3
  • 13
  • 1
    Create an index using the `_id.date` value explicitly. That is really what the error is telling you. You can add an `allowDiskUse` option as the error "also" tells you, but it's really the "index" that will make the most difference. And by the way "I don't want to" is not valid. Mother says "Eat your vegetables", so do it. But create the index first. – Neil Lunn May 07 '18 at 12:52
  • Thanks. That helped. "I don't want to" - I have the real reason to say so. Our system is real-time, so we must optimize all queries or redesign db. Current timing is not good, but you sent me on the correct way to find final decision. Thanks a lot. – Oleg May 07 '18 at 13:40

0 Answers0