CODEWITHSUNDEEP
mongodb
crazybob
crazybob

Reputation: 2317

MongoDB sort all and get specific range

I'm using mongoDB. I have a collection with:

String user_name,
Integer score

I would like to make a query that gets a user_name. The query should be sorted by score which returns the range of the 50 documents which the requested user_name is one of them.

For example, if I have 110 documents with the user_name X1-X110 with the scores 1-110 respectively and the input user_name was X72 I would like to get the range: X51-X100

EDIT:

An example of 3 documents: 

{ "user_name": "X1", "score": 1}
{ "user_name": "X2", "score": 2}
{ "user_name": "X3", "score": 3}

Now if I have 110 documents as described above, and I want to find X72 I want to get the following documents:

{ "user_name": "X50", "score": 50}
{ "user_name": "X51", "score": 51}
...
{ "user_name": "X100", "score": 100}

How can I do it?

Clarification: I don't have each document rank stored. What I do have is document scores, which aren't necessarily consecutive (the example is a little bit misleading). Here's a less misleading example:

{ "user_name": "X1", "score": 17}
{ "user_name": "X2", "score": 24}
{ "user_name": "X3", "score": 38}

When searching for "X72" I would like to get a slice of size 50 in which "X72" resides according to its rank. Again, the rank is not the element score, but the element index in a hypothetical array sorted by scores.

Upvotes: 1

Views: 3866

Answers (3)

Nilesh Rajani
Nilesh Rajani

Reputation: 538

Unfortunately, there is no direct way to achieve what you want. You will need some processing at your client end to figure out the range. First fetch the score by doing simple findOne / find

db.sample.findOne({"user_name": "X72"})

Next, using the score value (72 in this case), calculate the range in your client

lower = 72/50 => lower = 1.44

extract the number before decimal and set it to lower

lower = 1
upper = lower+1 => upper = 2

Now multiply the lower and upper values by 50 in your client, which would give you below values.

lower = 50
upper = 100

pass the lower and upper values to find and get the desired list.

db.sample.find({score:{$gt:50,$lte:100}}).sort({score:1})

Partial solution with one query:

I tried to do this with one query, but unfortunately I could not complete it. I am providing details below in hope that someone may be able to expand on this and complete what I started. Following are the steps that I planned:

  1. project the documents to divide all scores by 50 and store in a new field _score. (This is as far as I got)
  2. extract the value before decimal from _score [Stuck here] (Currently, I did not find any way to do this)
  3. group values based on _score. (each group will give you one slot)

  4. find and return the group where your score belongs (by using $match in aggregation pipeline)

    db.sample.aggregate([{$project:{_id:1, user_name:1,score:1,_score:{$divide:["$score",50]}}}])

I would be really interested to see how this is done!!!

Upvotes: 0

Philipp
Philipp

Reputation: 69663

Check out the MongoDB cursor operations sort, limit and skip. When used in conjunction, they can be used to get elements n to m which match your query:

 cursor = db.collcetion.find({...}).sort({score:1}).limit(100).skip(50);

This should return documents 51 to 100 in order of score.

Upvotes: 2

Philipp
Philipp

Reputation: 69663

When I understood you correctly, you want to query the users which are scorewise in the neighbourhood of another player.

With three queries you can select the user, the 25 users above it and the 25 users below.

First, you need to get the user itself and its score.

 user = db.collection.findOne({user_name: "X72"});

Then you select the next 25 players with scores above them:

 cursor db.collection.find(score: { $gt:user.score}).sort(score: -1 ).limit(25);
 //... iterate cursor

Then you select the next 25 players with scores below them:

 cursor db.collection.find(score: { $lt:user.score}).sort(score: 1 ).limit(25);
 //... iterate cursor

Upvotes: 0

Related Questions