Elasticsearch - Rank userIds based on score

Question

I'm trying to migrate some of the queries of our old MySQL database to our new Elasticsearch setup. The data is a little bit more complex but boils down to the following: I've got an index containing a lot of scores. Each score represents the points a player scored in a particular game. ``` { "userId": 2, "scoreId": 3457, "game": { "id": 6, "name": "scrabble" }, "date": 1340047100, "score": 56, // and more game data } ``` `scoreId` is the unique id for this score, `game.id` is the id of that type of game. ``` { "userId": 6, "gameId": 3479, "game": { "id": 5, "name": "risk" }, "date": "1380067200", "score": 100, // and more game data } ``` Over the years a lot of different games are played and I would like to rank the best players for each type of game. The ranking is based on the best 6 games of each player. So for example, if a player played scrabble 10 times, only its 6 best scores count for its total score. I would like to create a list like: ``` // Scrabble ranking: # | user | tot…

Accepted Answer

Using `top_hits` will not let you achieve what you need, because you cannot act upon the fields that are returned for each documents in the top hits aggregation.

One way to get around this is to use a top-level `terms` aggregation for users (as you did) and then for each user another `terms` sub-aggregation for the scores that you can sort in decreasing order and taking only the 6 best ones. Finally, using a pipeline `sum_bucket` aggregation, you can sum up those 6 scores for each user.

```
POST /my-index/scores/_search    
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "game.id": 6
          }
        }
      ]
    }
  },
  "aggs": {
    "users": {
      "terms": {              <--- segment by user
        "field": "userId"
      },
      "aggs": {
        "best_scores": {
          "terms": {          <--- 6 best scores for user
            "field": "score",
            "order": {
              "_term": "desc"
            },
            "size": 6
          },
          "aggs": {
            "total_score": {
              "sum": {
                "field": "score"
              }
            }
          }
        },
        "total_points": {     <--- total points for the user based on 6 best scores
          "sum_bucket": {
            "buckets_path": "best_scores > total_score"
          }
        }
      }
    }
  }
}
```

Note that one drawback of this solution is if the user had twice the exact same score, you'll get the 7 best scores and not the 6 best ones and the `total_score` value will be too high. We could use the `avg` instead of `sum` metric aggregation, but if we do this, we'll ignore one of the score occurrence, which is not good either.

Also note that it would be ideal to sort the users according to their `total_points` value, but it is not possible to sort using pipeline aggregations (since they run after the reduce phase). The sorting will need to happen on the client side.

Elasticsearch - Rank userIds based on score

Solution

Problem