Finding if a persons record is fastest among different levels

Working with Data
1

So I have a collection of records and I want to find all the records where Player X has the fasted record.

{ name: "Player1", level: 1, record 5}
{ name: "Player1", level: 2, record 15}
{ name: "Player1", level: 3, record 10}

{ name: "Player2", level: 1, record 6}
{ name: "Player2", level: 2, record 16}
{ name: "Player2", level: 3, record 9}

{ name: "Player3", level: 1, record 10}
{ name: "Player3", level: 2, record 20}
{ name: "Player3", level: 3, record 30}

output:
player1’s = {{ name: “Player1”, level: 1, record 5}, { name: “Player1”, level: 2, record 15}}
player2’s = {{ name: “Player2”, level: 3, record 9}}
player3’s = {}

What would be the best method to do this?

2

Just to add, the 3 outputs at the bottom are completely separate. I only want one player’s at a time.

3

We cannot insert you sample documents in our installation because the syntax is wrong. There is a missing colon for the record field. Please republish so that we can cut-n-paste the documents directly. It is faster for you to do that than for us to manually do correction before we start helping you.

In addition, what have you tried so far? What was the result? Any error messages?

With a quick look, you will need $sort on record, $group on level keeping the $first and then $matching on name.

4 
[{ name: "Player1", level: 1, record: 5},
{ name: "Player1", level: 2, record: 15},
{ name: "Player1", level: 3, record: 10},
{ name: "Player2", level: 1, record: 6},
{ name: "Player2", level: 2, record: 16},
{ name: "Player2", level: 3, record: 9},
{ name: "Player3", level: 1, record: 10},
{ name: "Player3", level: 2, record: 20},
{ name: "Player3", level: 3, record: 30}]

I’ve tried what you have said, but I don’t fully understand how $group works even after reading the documentation.

5

So this is my pipeline so far. I’m sure I am not understanding fully how group works. I used $push to keep the fields in the document but they are in a single array by themselves. I also realized I missed a field in the original set of docs.

Documents

[{name: "Player1", code: "test1", level: 1, record: 5},
{name: "Player1", code: "test1", level: 2, record: 5},
{name: "Player1", code: "test2", level: 1, record: 5},
{name: "Player1", code: "test2", level: 2, record: 5},
{name: "Player2", code: "test1", level: 1, record: 10},
{name: "Player2", code: "test1", level: 2, record: 1},
{name: "Player2", code: "test2", level: 1, record: 1},
{name: "Player2", code: "test2", level: 2, record: 10}]

Pipeline

[{$sort: {
 record: 1
}}, {$group: {
 _id: {
  level: '$level',
  record: '$record',
  code: '$code'
 },
 record: {
  $first: '$record'
 },
 code: {
  $push: '$code'
 },
 level: {
  $push: '$level'
 }
}}, {$match: {
 name: '$name'
}}]

Each “code” has different levels (which could be named the same e.g. code: test1, level: 1 VS. code: test2, level: 1)
I want a list of documents that have the highest score for for each level for each code.

6

correction *fastest time (lowest) NOT highest. :slight_smile:

7

Then, I recommend you take M121 from MongoDB University.

8

You do not want record in your _id.

You do not need to $push code or level. They are part of your _id.

Hence the $sort:{record:1} combine with $first:‘$record’.

9

@nebula, is your issue resolve? If it is, please mark my post as the solution so that others know that it works. This will help keep this post useful and efficient. It is simply courtesy.