# Nested aggregation prints different value than I expected

**URL:** <https://discuss.elastic.co/t/nested-aggregation-prints-different-value-than-i-expected/177804>\
**Category:** Elasticsearch\
**Created:** [April 22, 2019, 6:39am UTC](https://discuss.elastic.co/t/nested-aggregation-prints-different-value-than-i-expected/177804 "2019-04-22T06:39:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![b73048dd6c58ce804425](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/b73048dd6c58ce804425/32/44532_2.png) [@b73048dd6c58ce804425](https://discuss.elastic.co/u/b73048dd6c58ce804425)\
**Post date:** [April 22, 2019, 6:39am UTC](https://discuss.elastic.co/t/nested-aggregation-prints-different-value-than-i-expected/177804/1 "2019-04-22T06:39:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 20, 2019, 6:48am UTC](https://discuss.elastic.co/t/nested-aggregation-prints-different-value-than-i-expected/177804/2 "2019-05-20T06:48:19Z")

</div>

Closed on May 20, 2019, 6:48am UTC

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