4

I'd like to retrieve a random set of documents from a MongoDB database. So far after lots of Googling, I've only seen ways to retrieve one random document OR a set of documents starting at a random skip position but where the documents are still sequential.

I've tried mongoose-simple-random, and unfortunately it doesn't retrieve a "true" random set. What it does is skip to a random position and then retrieve n documents from that position.

Instead, I'd like to retrieve a random set like MySQL does using one query (or a minimal amount of queries), and I need this list to be random every time. I need this to be efficient -- relatively on par with such a query with MySQL. I want to reproduce the following but in MongoDB:

SELECT * FROM products ORDER BY rand() LIMIT 50;

Is this possible? I'm using Mongoose, but an example with any adapter -- or even a straight MongoDB query -- is cool.

I've seen one method of adding a field to each document, generating a random value for each field, and using {rand: {$gte:rand()}} each query we want randomized. But, my concern is that two queries could theoretically return the same set.

Chad Johnson
  • 21,215
  • 34
  • 109
  • 207

2 Answers2

2

You may do two requests, but in an efficient way :

  1. Your first request just gets the list of all "_id" of document of your collections. Be sure to use a mongo projection db.products.find({}, { '_id' : 1 }).
  2. You have a list of "_id", just pick N randomly from the list.
  3. Do a second query using the $in operator.

What is especially important is that your first query is fully supported by an index (because it's "_id"). This index is likely fully in memory (else you'd probably have performance problems). So, only the index is read while running the first query, and it's incredibly fast.

Although the second query means reading actual documents, the index will help a lot.

If you can do things this way, you should try.

dotpush
  • 428
  • 3
  • 14
  • If I have 500,000 documents in my collection, would this still be efficient? – Chad Johnson Dec 08 '14 at 07:05
  • @ChadJohnson nah, not even close, you would need a separate field: http://stackoverflow.com/questions/2824157/random-record-from-mongodb try looking at anything but the first answer there – Sammaye Dec 08 '14 at 17:50
  • @Chad Johnson : The best way of knowing it is probably to try on your collection. For the first request, to achieve your goal (really random documents), you should not use a limit. However, if you just want to test that the first request does not imply something too intensive on your production system, you may try it with a limit of 1000, then 5000, 25000... Until you reach the number of documents in your collection and confirmed everything is correct. – dotpush Dec 08 '14 at 17:54
  • @Sammaye : Could you link to the specific answer that, according to you, does the job the best way to pick N (for example 50) random documents ? – dotpush Dec 08 '14 at 18:03
  • http://stackoverflow.com/a/5517206/383478 would work quite well with some changes for this specific scenario, straight indexed query pulling out only what is needed – Sammaye Dec 08 '14 at 18:07
  • @Sammaye : [your choosen answer](http://stackoverflow.com/a/5517206/383478) seems interesting when querying ONE random document at a time. It's not as easy at all to get multiple random documents, efficiently this way. – dotpush Dec 08 '14 at 18:15
  • It would be a lot more efficient than pulling out 500,000 _ids and would actually scale I reckon. I have never needed to do rand() rows before but I am sure I could pull this off for 50 random rows. – Sammaye Dec 08 '14 at 18:39
0

I don't think MySQL ORDER BY rand() is particularly efficient - as I understand it, it essentially assigns a random number to each row, then sorts the table on this random number column and returns the top N results.

If you're willing to accept some overhead on your inserts to the collection, you can reduce the problem to generating N random integers in a range. Add a counter field to each document: each document will be assigned a unique positive integer, sequentially. It doesn't matter what document gets what number, as long as the assignment is unique and the numbers are sequential, and you either don't delete documents or you complicate the counter document scheme to handle holes. You can do this by making your inserts two-step. In a separate counter collection, keep a document with the first number that hasn't been used for the counter. When an insert occurs, first findAndModify the counter document to retrieve the next counter value and increment the counter value atomically. Then insert the new document with the counter value. To find N random values, find the max counter value, then generate N distinct random numbers in the range defined by the max counter, then use $in to retrieve the documents. Most languages should have random libraries that will handle generating the N random integers in a range.

wdberkeley
  • 11,531
  • 1
  • 28
  • 23
  • 1
    "as long as the assignment is unique and the numbers are sequential" -> I'd add also as long as documents are never deleted. – dotpush Dec 08 '14 at 18:11
  • Doesn't rand() actually pick from a AI key on the table if I remember correctly? – Sammaye Dec 08 '14 at 18:36
  • @dotpush - excellent point. It does require docs aren't deleted. I've edited the answer. You could make the numbering scheme more complicated to allow deletions. I think it might be easier just to do single random draws than have to structure the use of the collection around drawing samples, for many use cases. – wdberkeley Dec 10 '14 at 16:07