[Collection Query] Performance to get last for may records of Collection?

#1

I have many records of Collection.

Collection = [
  {item: 'A', date: '2018-07-01', qtybalance: 100},
  {item: 'A', date: '2018-07-02', qtybalance: 70},
  {item: 'A', date: '2018-07-03', qtybalance: 150},
  // .................Many of A..............
  {item: 'B', date: '2018-07-01', qtybalance: 100},
  {item: 'B', date: '2018-07-02', qtybalance: 70},
  {item: 'B', date: '2018-07-03', qtybalance: 120},
  // ...............Many of B...............
]

I would like to get last record for Item (Distinct) as at 2018-07-03 with Performance like Collection Index.
My result expected (2 records)

{item: 'A', date: '2018-07-03', qtybalance: 150},
{item: 'B', date: '2018-07-03', qtybalance: 120},

Please help me for Collection Query???

#2

What I would do here is make the date an actual date instead of a string and set a descending index on that key, then you can query with a sort and limit naturally…

SomeCollection.find({}, {
  sort: { date: -1 },
  limit: 2,
});
#3

@copleykj, thanks for your reply.
It find in many records or 2 records, If we explain query with .explain("executionStats").

#4

You need to do aggregation and using $group in the pipeline

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