How to find 'N' random records from collection

[Mit Mehta]

Hi,

I want to retrieve 'N' (lets say 15 records) random records from particular collection. How can I get this? Please help me. Its very urgent.

[Rhys Campbell]

I don't know of anything similar ala SQL...

SELECT id

FROM myTable

ORDER BY RAND()

LIMIT 15;

Here's as close as I can think of...

use local

var n = db.oplog.rs.count();

var s = Math.floor(Math.random() * n);

db.oplog.rs.find().skip(s).limit(15);

There's number of things wrong with this... Start is random but next 15 records are returned in natural order. It's also possible to get less than 15 depending on skip point. Other than making 15 separate calls I'm not sure there's anything better.

[Stephen Steneker]

On Wednesday, 29 July 2015 21:35:00 UTC+10, Mit Mehta wrote:

I want to retrieve 'N' (lets say 15 records) random records from particular collection. How can I get this? Please help me. Its very urgent.

Hi Mit,

There currently (as at MongoDB 3.0) is no server-side feature to choose random documents. However, there is work in progress to add a $sample aggregation operator in the unstable/development branch of MongoDB 3.1. See https://jira.mongodb.org/browse/SERVER-533 and related issues.

In the interim, there are some other approaches to consider depending on your requirements:

 - choose a random skip() value based on the count of documents in a collection (not overly efficient, particularly for large skip values)

 - add random values to documents that can be indexed and used for selection (more efficient for large collections, but possible caveats on distribution bias): https://github.com/mongodb/cookbook/blob/master/content/patterns/random-attribute.txt

 - more ideas and discussion: http://stackoverflow.com/questions/2824157/random-record-from-mongodb

Regards,

Stephen

[Stephen Steneker]

On Wednesday, 29 July 2015 23:10:53 UTC+10, Rhys Campbell wrote:

Here's as close as I can think of...

use local

var n = db.oplog.rs.count();

var s = Math.floor(Math.random() * n);

db.oplog.rs.find().skip(s).limit(15);

There's number of things wrong with this... Start is random but next 15 records are returned in natural order. It's also possible to get less than 15 depending on skip point. Other than making 15 separate calls I'm not sure there's anything better.

Hi Rhys,

You definitely would not want to use the oplog to find random documents from a given collection. The oplog is a special capped collection designed for used by replication.

There are a number of reasons why the oplog wouldn't be a suitable approach for random selection, including:

 - the oplog is a fixed size and over time only includes recent data changes (to the limit of the oplog size)

 - oplog documents would have to be parsed in order to find the original document

 - each update to a document creates a new oplog entry (so this would strongly bias recent documents with many updates)

 - the oplog has no indexes (and does not support adding any)

Regards,

Stephen

[Mit Mehta]

Thanks a lot Stephen Steneker :)

Thanks & Regards,
Mit Mehta

[Rhys Campbell]

Don't panic. It's just an example to illustrate the mechanics of the java script. I just happen to use the oplig as an example.

Rhys