CODEWITHSUNDEEP
elasticsearchaggregate
passkey1510
passkey1510

Reputation: 152

Elasticsearch - Aggregation sort by metric

I have the following documents:

POST index/type
{
  "profile": {
    "tracks": [
      {
        "title": "Track 1",
        "count": 5
      },
      {
        "title": "Track 2",
        "count": 3
      }
    ]
  }
}

POST index/type
{
  "profile": {
    "tracks": [
      {
        "title": "Track 2",
        "count": 8
      },
      {
        "title": "Track 3",
        "count": 1
      }
    ]
  }
}

POST index/type
{
  "profile": {
    "tracks": [
      {
        "title": "Track 4",
        "count": 20
      },
      {
        "title": "Track 5",
        "count": 1
      }
    ]
  }
}

where "count" property in each track item is the number of times user has played that particular track.

I would like to have a list of tracks order by number of times they are played. So in the example above, expected results would be:

[
  {
    "key": "Track 4",
    "count": {
       "value": 20
    }
  },
  {
    "key": "Track 2",
    "count": {
       "value": 11
    }
  },
  {
    "key": "Track 1",
    "count": {
       "value": 5
    }
  },
  {
    "key": "Track 3",
    "count": {
       "value": 1
    }
  },
  {
    "key": "Track 5",
    "count": {
       "value": 1
    }
  }
]

I have tried:

"aggs": {
  "track_count": {
    "terms": {
        "field": "profile.tracks.title",
        "order": {
            "count": "desc"
        }
    },
    "aggs": {
        "count": {
            "sum": {
                "field": "profile.tracks.count"
            }
        }
    }
  }
}

But it does not seem to be correct since the the count is calculated by summing all other count properties of each track item.

I do not know how to build my aggregation so that the count value is calculated by summing count properties of the same track across other documents.

Any help will be greatly appreciated. Thank you.

Upvotes: 1

Views: 230

Answers (1)

keety
keety

Reputation: 17441

In the mapping make sure tracks is of nested-type.

Example :

put test/test/_mapping 
{
    "properties" : {
        "profile" : {
            "properties" : {
                "tracks" : {
                    "type" : "nested" ,
                     "properties" : {
                        "title" : 
                        {
                            "type" : "string",
                            "index" : "not_analyzed"
                        },
                        "count" :{ 
                            "type" : "integer"
                        }
                     }
                }

            }
        }
    }
}

once marked as nested one can then use nested aggregations to achieve the above .

Example:

{
   "size": 0,
   "aggs": {
      "tracks_agg": {
         "nested": {
            "path": "profile.tracks"
         },
         "aggs": {
            "tracks": {
               "terms": {
                  "field": "profile.tracks.title",
                  "order": {
                     "count": "desc"
                  }
               },
               "aggs": {
                  "count": {
                     "sum": {
                        "field": "profile.tracks.count"
                     }
                  }
               }
            }
         }
      }
   }
}

Upvotes: 1

Related Questions