0

I would like to retrieve a list that contains an specified record under some conditions and only retrieve a number of records before and a number of records after that record. Are there any solutions?

For example, I have a MongoDB schema { id, date, section } Data set:

100, 26 Aug 2014 11:00, A
110, 26 Aug 2014 11:01, A
140, 26 Aug 2014 12:00, A
141, 27 Aug 2014 12:00, B
200, 30 Aug 2014 11:00, A
210, 01 Sep 2014 11:01, B
290, 02 Sep 2014 12:00, A
300, 26 Sep 2014 12:00, A
301, 27 Oct 2014 12:00, B
302, 30 Oct 2014 11:23, A
410, 01 Oct 2014 15:01, B
590, 02 Oct 2014 12:00, A
600, 26 Nov 2014 00:00, A

I would like to get a list, which contains an unique id = 300 and 3 records before and 3 records after that record with id = 300, sorted by date under section A.

The output:

140, 26 Aug 2014 12:00, A
200, 30 Aug 2014 11:00, A
290, 02 Sep 2014 12:00, A
300, 26 Sep 2014 12:00, A <-- middle
302, 30 Oct 2014 11:23, A
590, 02 Oct 2014 12:00, A
600, 26 Nov 2014 00:00, A

I have a stupid approach:

  1. get the date (let say it's 26 Sept 2014 12:00) of the specified id = 300 with section A
  2. set a query to find records that the date is greater than and equal to 26 Sept 2014 12:00 ordered by date, limited by 3 records.
  3. set a query to find records that the date is less than 26 Sept 2014 12:00 ordered by date, limited by 3 records.
  4. combine two lists.

Is there any better approaches to just retrieve this kind of list in a query or in better performance? Thank you.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
fmchan
  • 760
  • 1
  • 11
  • 29
  • Do ids have same direction with dates? For example, id(300) > id(290), then date(300) >= date(290). And, is it possible that duplicate date for different ids? – Wizard Aug 27 '14 at 09:48
  • @Wizard id is unique and suppose new record can be inserted as {610, 26 Oct 2014 00:00, A} – fmchan Aug 27 '14 at 09:59
  • 1
    So, it's impossible to find in one query by id because date is unknown. Your approaches should be a good one for this situation, and you can better the performance by add index, such as `{section:1, date:1, id:1}`. – Wizard Aug 27 '14 at 10:11
  • 1
    Please more precisely define what the ordering is. What defines before or after? Is it `_id`s? Is it chronological order? If it's chronological order, then to find an interval about a given document whose extent is determined by the count of documents within, you'll have to do your "stupid" approach (which I don't think is stupid). But you also might want to consider a different type of query with a more natural restriction like defining the extent by start and end dates, depending on your use case. – wdberkeley Aug 27 '14 at 14:14
  • @Wizard thank you. then I will implement my approach and set kinda index – fmchan Aug 28 '14 at 01:25
  • @wdberkeley yup, it's chronological order. thank you for your recommendation to set the range of date. – fmchan Aug 28 '14 at 01:28
  • @wdberkeley, fmchan's question let think another question about index. Could you please help to see [this](http://stackoverflow.com/questions/25539800/is-it-possible-to-reverse-the-traversing-order-of-index-in-mongodb-to-keep-perfo)? Thanks. – Wizard Aug 28 '14 at 10:59

2 Answers2

-1

$skip and $limit in aggregation framework

Here is a good example of using skip and limit which should help you achieve the SELECT TOP X or LIMIT X

Note: I'm assuming you want to use the aggregate framework based on the tagging of this question.

I believe this should do it

x = 300;
db.user.aggregate({$match : { "id" : {$lte: x+10,$gte: x-10 }},{$sort : {"date": 1 }}});
Community
  • 1
  • 1
rajh2504
  • 1,266
  • 2
  • 21
  • 37
  • I have read but I am plighting of forming the aggregation function. Do you have any ideas? – fmchan Aug 27 '14 at 06:31
  • I added an example of how you might go about getting your range based on the id. – rajh2504 Aug 28 '14 at 02:45
  • thank you for your answer. However, as you can see the example I have shown, order should be the date but not id and suppose new record can be inserted as {610, 26 Oct 2014 00:00, A} – fmchan Aug 28 '14 at 06:55
  • there you go, added the date sort to the query. – rajh2504 Sep 01 '14 at 18:11
-1

Let's your schema name be USER. You could use below mongo db query to fetch the result

$sort function :

  • 1 represents ascending
  • -1 represents descending

Refer documentation : $Sort Documentation

Query will be :

db.user.aggregate({$match : { "id" : 300}},{$sort : {"date": 1 }},{$skip : 0},{$limit : 10});

$skip value will be your limit value after first query

db.user.aggregate({$match : { "id" : 300}},{$sort : {"date": 1 }},{$skip : 10},{$limit : 10});

Refer Documentation : Aggregation Documentation

Newbie
  • 1,403
  • 2
  • 13
  • 22
  • Thank you. However, I think you misunderstand my question. you are going to find out all result matching id = 300 but id = 300 is actually unique, and what i want to get is 10 records before id = 300 and 10 records after id = 300. – fmchan Aug 27 '14 at 06:17