solrsolrcloud

Combining facet and stats results from three Solr queries

I have two solr queries one is faceted and another one is a stats query over a same parameter. I would like to combine results from both queries in Solr.

Imagine a game database, we want to get statistics for all games. Game session doc has fields

game_id, user_id, country, play_time, completion_rate

Here are example of those queries

Total unique plays for games from USA q=:&facet=true&facet.pivot=game_id,user_id&fq=country:USA

Result:

GAMEID  |  TOTAL_UNIQUE_PLAYS
   1    |       10
   2    |       15

Average played time for games from USA

q=:&stats=true&stats.facet=game_id&stats.field=play_time&fq=country:USA

 Result:
    GAMEID    |   AVG_PLAYTIME
       1      |       100
       2      |       150

Average completion rate games from USA

q=:&stats=true&stats.facet=game_id&stats.field=completion_rate&fq=country:USA

Result:
GAMEID    |   AVG_COMPLETION_RATE
   1      |       50
   2      |       100

I would like to combine all 3 results as following

   GAMEID   |   TOTAL_UNIQUE_PLAYS   |   AVG_PLAYTIME   | AVG_COMPLETION_RATE
       1    |       100              |         10       |         50
       2    |       150              |         15       |         100

How can I do this in Solr and if I would like to add more queries to it ?

Solution

  • The JSON Facet API will let you add multiple aggregations under a bucket:

    {
  "query": "*:*",
  "facet": {
    "categories":{
      "type": "terms",
      "field": "GAMEID",
      "limit": 20,
      "facet": {
        "total_unique_players": "unique(user_id)",
        "avg_playtime": "avg(playtime)",
        "avg_completion_rate": "avg(completion_rate)",
      }
    }
  }
}

    The aggregation functions should then be applied for each unique term in GAMEID, giving you the averages and unique count per game.