Calculate Average of each category in Array

[WatsonS]

Hello Everyone,

I'm building an accommodation rating system where one rating input contain 5 categories with the example structure collected in an array:

"submitted_rating" : [
{
  "rating": 4,
  "title": "Travel"
},
{
  "rating": 3,
  "title": "Restaurants"
},
{
  "rating": 1.5,
  "title": "Facility"
},
{
  "rating": 3.5,
  "title": "Quality"
},
{
  "rating": 5,
  "title": "price"
}
]

Then I tried to use aggregation to calculate the "average rating in each category" based on the follow example data:

I would expecting following results:

I was using below aggregation, but the average results I got was not the results I'm looking for.

 "aggs": {
"submitted_rating": {
  "terms": {
    "field": "submitted_rating.title.keyword"
  },
  "aggs": {
    "rating": {
      "avg": {
        "field": "submitted_rating.rating"
      }
    }
  }
}

It seems like the returned avg value is the average of all "rating" across different "title" as well.

What did I do wrong?

Thanks!

[flash1293 (Joe Reuter)]

Hey, you did nothing wrong, this is the expected behavior at the moment. Kibana treats your nested object as if they were flattened, which means you actually have to arras in each document:

"submitted_rating.title.keyword": ["Travel", "Restaurants", "Facility", "Quality", "price"],
"submitted_rating.rating": [4, 3, 1.5, 3.5, 5],

For the aggregation there is no relation between the values of the first array and the values of the second array.

Elasticsearch should be able to do this if the ratings are configured as nested fields in the mapping, but Kibana does not support this at the moment.

Currently the only way to make Kibana aware of the connection is to ingest the individual rating categories as individual documents, then the aggregation should produce the expected results.