Elasticsearch - rating userIds based on rating

Question

I am trying to transfer some of the queries from our old MySQL database to our new installation of Elasticsearch. The data is a little more complicated, but it comes down to the following:

I have an index containing many points. Each point represents points scored by a player in a particular game.

{
  "userId": 2,
  "scoreId": 3457,
  "game": {
    "id": 6,
    "name": "scrabble"
  },
  "date": 1340047100,
  "score": 56,
  // and more game data
}

scoreIdis a unique identifier for this rating, game.idis an identifier for this type of game.

{
  "userId": 6,
  "gameId": 3479,
  "game": {
    "id": 5,
    "name": "risk"
  },
  "date": "1380067200",
  "score": 100,
  // and more game data
}

Many different games have been played over the years, and I would like to rate the best players for each type of game. The rating is based on the top 6 games of each player. So, for example, if a player played scrabble 10 times, only his 6 best points calculate his total score.

I would like to create a list like:

// Scrabble ranking:
# | user | total points  
1 |  2   | 4500
2 |  6   | 3200
2 |  23  | 1500

, MySQL , , 6 . , , , .

, , , . , - , . , :

GET /my-index/scores/_search
{
  "query": {
    "bool": {
      "filter": [
        {"term": { "game.id": 6 }}
      ]
    }
  },
  "aggs": {
    "scores": {
      "terms": {
        "field": "userId"
      }
    },
    "top_scores_user": {
      "top_hits": {
        "sort": [{
          "score": {
            "order": "desc"
          }
        }],
        "size" : 6
      }
    }
  },
   "size": 0
}

2.3, , , .

Answer 1

top_hits , , , .

- terms ( ), terms , 6 . , sum_bucket, 6 .

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"
          }
        }
      }
    }
  }
}

, , , 7 , 6 , total_score . avg sum, , , .

, total_points, ( ) . .