Aggregate not working as expected (ES VERSION : 6.2.2)

Elastic Stack Elasticsearch
1

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.

2

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

3

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
}
}
}
}
}

4

I found the following issue that might help you:

You need to add a filter to your aggregation:

  "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"
              }
            }
          }
        }
      }
    }
  }
}
5

Thanks a lot!! This worked for me.

6

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!!

7

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