# Aggregate not working as expected (ES VERSION : 6.2.2)

**URL:** <https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487>\
**Category:** Elasticsearch\
**Created:** [March 29, 2019, 7:31am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487 "2019-03-29T07:31:06Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![sur1029](https://avatars.discourse-cdn.com/v4/letter/s/e274bd/32.png) [@sur1029](https://discuss.elastic.co/u/sur1029)\
**Post date:** [March 29, 2019, 7:31am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/1 "2019-03-29T07:31:06Z")

</div>

Hi,  
I have putting data in elastic search something like this:  
Data 1:  
{  
"app": "pigeon",  
"time": "2019-03-28T06:22:02.175517",  
"email": [  
{ "flow\_name" : "AuthOTP",  
"1": 80,  
"2": 79,  
"3": 12,  
"4": 60  
}  
]  
,  
"sms": [  
{ "flow\_name" : "Booking\_Voucher",  
"1": 20,  
"2": 17  
},  
{ "flow\_name" : "AuthOTP",  
"1": 80,  
"2": 79,  
"3": 12  
}  
],  
"message": "STATS\_PER\_TAG",  
"cd": "2019-03-28T11"  
}

Data 2:  
{  
"app" : "pigeon",  
"time" : "2019-03-28T06:22:02.175517",  
"sms" : [  
{  
"flow\_name" : "AuthOTP",  
"4" : 60

```
}
],
"message": "STATS_PER_TAG",
"cd": "2019-03-28T11"

```

}

I want aggregate count of "1" , "2", "3" and "4" per flow name.  
My query  
{  
"\_source" : false,  
"query": {  
"nested": {  
"path": "sms",  
"query": {  
"bool": {  
"must": [  
{ "match": { "sms.flow\_name": "AuthOTP" }}  
]  
}  
},  
"inner\_hits": {}

```
}

```

},  
"aggs" : {  
"queued" : {  
"sum" : { "field" : "sms.1" }},  
"sent" : {  
"sum" : { "field" : "sms.2" }  
},  
"failed" : {  
"sum" : { "field" : "sms.3" }  
},  
"delivered" : {  
"sum" : { "field" : "sms.4" }  
}  
}

}

This is returning aggregate count of all sms irrespective of flow names.  
Please Help.

---

<div class="post-metadata">

**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\
**Post date:** [March 29, 2019, 10:09am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/2 "2019-03-29T10:09:29Z")

</div>

It is not possible to reproduce without the mapping.

What it struck me is that it seems that sms is nested so you probably need to use a nested aggregation:

[https://www.elastic.co/guide/en/elasticsearch/reference/6.2/search-aggregations-bucket-nested-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.2/search-aggregations-bucket-nested-aggregation.html)

---

<div class="post-metadata">

**Author:** ![sur1029](https://avatars.discourse-cdn.com/v4/letter/s/e274bd/32.png) [@sur1029](https://discuss.elastic.co/u/sur1029)\
**Post date:** [March 29, 2019, 10:26am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/3 "2019-03-29T10:26:13Z")

</div>

I am already using nested type in mappings  
Here are my mappings  
{  
"mappings": {  
"doc": {  
"properties": {  
"email": {  
"type": "nested",  
"include\_in\_parent": true  
},  
"sms": {  
"type": "nested",  
"include\_in\_parent": true  
}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\
**Post date:** [March 29, 2019, 11:08am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/4 "2019-03-29T11:08:02Z")

</div>

I found the following issue that might help you:

> <https://github.com/elastic/elasticsearch/issues/28262>

You need to add a filter to your aggregation:

```auto
  "aggs": {
    "nested": {
      "nested": {
        "path": "sms"
      },
      "aggs": {
        "OnlyAuthOTP": {
          "filter": {
            "match": {
              "sms.flow_name": {
                "query": "AuthOTP"
              }
            }
          },
          "aggs": {
            "queued": {
              "sum": {
                "field": "sms.1"
              }
            },
            "sent": {
              "sum": {
                "field": "sms.2"
              }
            },
            "failed": {
              "sum": {
                "field": "sms.3"
              }
            },
            "delivered": {
              "sum": {
                "field": "sms.4"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![sur1029](https://avatars.discourse-cdn.com/v4/letter/s/e274bd/32.png) [@sur1029](https://discuss.elastic.co/u/sur1029)\
**Post date:** [April 1, 2019, 10:16am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/5 "2019-04-01T10:16:41Z")

</div>

> [@sur1029](#):
>
> {  
> "mappings": {  
> "doc": {  
> "properties": {  
> "email": {  
> "type": "nested",  
> "include\_in\_parent": true  
> },  
> "sms": {  
> "type": "nested",  
> "include\_in\_parent": true  
> }  
> }  
> }  
> }  
> }

Thanks a lot!! This worked for me.

---

<div class="post-metadata">

**Author:** ![sur1029](https://avatars.discourse-cdn.com/v4/letter/s/e274bd/32.png) [@sur1029](https://discuss.elastic.co/u/sur1029)\
**Post date:** [April 3, 2019, 10:46am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/6 "2019-04-03T10:46:32Z")

</div>

Hi Ignacio\_Verna,  
I had yet another use case on above query:  
Can i get all aggregates with single flow\_name but different channels in one single query.  
Channels here are sms and email  
Thanks for our help!!

---

<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 1, 2019, 10:46am UTC](https://discuss.elastic.co/t/aggregate-not-working-as-expected-es-version-6-2-2/174487/7 "2019-05-01T10:46:52Z")

</div>

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