Nested aggregation prints different value than I expected

Elastic Stack Elasticsearch
1

Hello.

I'm stuck on the nested aggregation problem, and need your help.

In my examples, there are two 'volunteer' documents, and they have five, two nested documents each.
I want to give equally divided score to each participants from 'total_score' value in 'volunteer' document.
And I want to get overall scores by each participants.

Jane must have doubled score on "class project" but it is not reflected in aggregation result.
What am I missing?

===== My Example code: =====

MAPPING

curl -XPUT -H 'Content-Type: application/json' http://my_elasticsearch_host:9200/_template/tmpl_volunteer -d'
{

"index_patterns": [ "volunteer-*" ],
"settings" : {
    "number_of_shards" : 3,
    "number_of_replicas" : 2
},
"mappings": {
    "volunteer": {
        "properties": {
            "name": { "type": "keyword" },
            "participants": {
                "type": "nested",
                "properties": {
                    "name": { "type": "keyword" },
                    "date": { "type": "date" }
                }
            },
            "total_score": { "type": "float" },
            "days": { "type": "integer" }
        }
    }
}
}'

PUT DATA

curl -XPUT -H 'Content-Type: application/json' http://my_elasticsearch_host:9200/volunteer-2019-01/volunteer/1 -d'
{
    "name": "class project",
    "participants": [
        { "name": "John", "date": "2019-01-01T09:00:00+00:00" },
        { "name": "Jane", "date": "2019-01-02T09:00:00+00:00" },
        { "name": "Smith", "date": "2019-01-03T09:00:00+00:00" },
        { "name": "Michael", "date": "2019-01-04T09:00:00+00:00" },
        { "name": "Jane", "date": "2019-01-05T09:00:00+00:00" }
    ],
    "total_score": 500.0,
    "days": 5
}'

curl -XPUT -H 'Content-Type: application/json' http://my_elasticsearch_host:9200/volunteer-2019-01/volunteer/2 -d'
{
    "name": "school project",
    "participants": [
        { "name": "John", "date": "2019-01-06T09:00:00+00:00" },
        { "name": "Jane", "date": "2019-01-07T09:00:00+00:00" }
    ],
    "total_score": 300.0,
    "days": 2
}'

QUERY

GET /volunteer-*/_search
{
  "query": {
    "match_all": {}
  },
  "aggregations": {
    "nested": {
      "nested": {
        "path": "participants"
      },
      "aggregations": {
        "by_participants": {
          "terms": {
            "field": "participants.name"
          },
          "aggregations": {
            "reverse_nested": {
              "reverse_nested": {},
              "aggregations": {
                "scoreSum": {
                  "sum": {
                    "field": "total_score",
                    "script": {
                      "source": "(float) _value / (float) doc['days'].value",
                      "lang": "painless"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

RESULT

{
  "took" : 18,
  "timed_out" : false,
  "_shards" : {
    "total" : 3,
    "successful" : 3,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "volunteer-2019-01",
        "_type" : "volunteer",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "name" : "school project",
          "participants" : [
            {
              "name" : "John",
              "date" : "2019-01-06T09:00:00+00:00"
            },
            {
              "name" : "Jane",
              "date" : "2019-01-07T09:00:00+00:00"
            }
          ],
          "total_score" : 300.0,
          "days" : 2
        }
      },
      {
        "_index" : "volunteer-2019-01",
        "_type" : "volunteer",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "name" : "class project",
          "participants" : [
            {
              "name" : "John",
              "date" : "2019-01-01T09:00:00+00:00"
            },
            {
              "name" : "Jane",
              "date" : "2019-01-02T09:00:00+00:00"
            },
            {
              "name" : "Smith",
              "date" : "2019-01-03T09:00:00+00:00"
            },
            {
              "name" : "Michael",
              "date" : "2019-01-04T09:00:00+00:00"
            },
            {
              "name" : "Jane",
              "date" : "2019-01-05T09:00:00+00:00"
            }
          ],
          "total_score" : 500.0,
          "days" : 5
        }
      }
    ]
  },
  "aggregations" : {
    "nested" : {
      "doc_count" : 7,
      "by_participants" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "Jane",
            "doc_count" : 3,
            "reverse_nested" : {
              "doc_count" : 2,
              "scoreSum" : {
                "value" : 250.0
              }
            }
          },
          {
            "key" : "John",
            "doc_count" : 2,
            "reverse_nested" : {
              "doc_count" : 2,
              "scoreSum" : {
                "value" : 250.0
              }
            }
          },
          {
            "key" : "Michael",
            "doc_count" : 1,
            "reverse_nested" : {
              "doc_count" : 1,
              "scoreSum" : {
                "value" : 100.0
              }
            }
          },
          {
            "key" : "Smith",
            "doc_count" : 1,
            "reverse_nested" : {
              "doc_count" : 1,
              "scoreSum" : {
                "value" : 100.0
              }
            }
          }
        ]
      }
    }
  }
}

RESULT and WHAT I EXPECTED

name scoreSum | expectedScoreSum
======= ========= | ================
Jane 250.0 350.0 <- (twice(in different days) * 100.0 on "class project", once * 150.0 on "school project")
John 250.0 250.0
Michael 100.0 100.0
Smith 100.0 100.0
======= ========= ================
SUM 700.0 800.0 <- (500.0 on "class project", 300.0 on "school project")

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.