How to select fields after aggregation in Elastic Search 2.3

Question

I have following schema for an index:-

 PUT
    "mappings": {
        "event": {
          "properties": {
            "@timestamp": { "type": "date",    "doc_values": true},
            "partner_id":  { "type": "integer", "doc_values": true},
            "event_id": { "type": "integer", "doc_values": true},
            "count": { "type": "integer", "doc_values": true, "index": "no" },
            "device_id": { "type": "string", "index":"not_analyzed","doc_values":true }
            "product_id": { "type": "integer", "doc_values": true},
          }
        }
      }

I need result equivalent to following query:-

SELECT product_id, device_id, sum(count) FROM index WHERE partner_id=5 AND timestamp<=end_date AND timestamp>=start_date GROUP BY device_id,product_id having sum(count)>1;

I am able to achieve the result by following elastic query:-

GET
{
    "store": true,
    "size":0,
    "aggs":{
        "matching_events":{
            "filter":{
                "bool":{
                    "must":[
                        {
                            "term":{
                                "partner_id":5
                            }
                        },
                        {
                            "range":{
                                "@timestamp":{
                                    "from":1470904000,
                                    "to":1470904999
                                }
                            }
                        }
                    ]
                }
            },
            "aggs":{
                      "group_by_productid": {
                      "terms":{
                        "field":"product_id"
                    },
            "aggs":{
                "group_by_device_id":{
                    "terms":{
                        "field":"device_id"
                    },
                    "aggs":{
                        "total_count":{
                            "sum":{
                                "field":"count"
                            }
                        },
                        "sales_bucket_filter":{
                            "bucket_selector":{
                                "buckets_path":{
                                    "totalCount":"total_count"
                                },
                                "script": {"inline": "totalCount > 1"}
                            }
                        }
                    }
                    }}
                }
            }
        }
    }

}'

However for the case where count is <=1 query is returning empty buckets with key as product_id. Now out of 40 million groups, only 100k will have satisfy the condition, so I am returned with huge result set, majority of which is useless. How can I select only particular field after aggregation? I tried this but not working- `"fields": ["aggregations.matching_events.group_by_productid.group_by_device_id.buckets.key"]

Edit:

I have following set of data:-

 device id                       Partner Id         Count 
db63te2bd38672921ffw27t82        367                   3
db63te2bd38672921ffw27t82        272                   1

I go this output:-

{

    "took":6,
    "timed_out":false,
    "_shards":{
        "total":5,
        "successful":5,
        "failed":0
    },
    "hits":{
        "total":7,
        "max_score":0.0,
        "hits":[
        ]
    },
    "aggregations":{
        "matching_events":{
            "doc_count":5,
            "group_by_productid":{
                "doc_count_error_upper_bound":0,
                "sum_other_doc_count":0,
                "buckets":[
                    {
                        "key":367,
                        "doc_count":3,
                        "group_by_device_id":{
                            "doc_count_error_upper_bound":0,
                            "sum_other_doc_count":0,
                            "buckets":[
                                {
                                    "key":"db63te2bd38672921ffw27t82",
                                    "doc_count":3,
                                    "total_count":{
                                        "value":3.0
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "key":272,
                        "doc_count":1,
                        "group_by_device_id":{
                            "doc_count_error_upper_bound":0,
                            "sum_other_doc_count":0,
                            "buckets":[
                            ]
                        }
                    }
                ]
            }
        }
    }

}

As you can see, bucket with key 272 is empty which make sense, but shouldn't this bucket be removed from result set altogether?

Answer 1

I've just found out that there is a fairly recent issue and PR that adds a _bucket_count path to a buckets_path option so that an aggregation can potentially filter the parent bucket based on the number of buckets another aggregation has. In other words if the _bucket_count is 0 for a parent bucket_selector the bucket should be removed.

This is the github issue: https://github.com/elastic/elasticsearch/issues/19553