# How to get aggregated data by a field from ES with two values accumulated

**URL:** <https://discuss.elastic.co/t/how-to-get-aggregated-data-by-a-field-from-es-with-two-values-accumulated/128192>\
**Category:** Elasticsearch\
**Created:** [April 16, 2018, 1:14pm UTC](https://discuss.elastic.co/t/how-to-get-aggregated-data-by-a-field-from-es-with-two-values-accumulated/128192 "2018-04-16T13:14:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![evgenygrin](https://avatars.discourse-cdn.com/v4/letter/e/a9a28c/32.png) [@evgenygrin](https://discuss.elastic.co/u/evgenygrin)\
**Post date:** [April 16, 2018, 1:14pm UTC](https://discuss.elastic.co/t/how-to-get-aggregated-data-by-a-field-from-es-with-two-values-accumulated/128192/1 "2018-04-16T13:14:55Z")

</div>

For the last two days my team deals with solving an issue of querying the data from Elasticsearch DB (ES). Our purpose is to get aggregated data by a field from ES with two values accumulated.  
If I would translate it to SQL query we need something like that:

`SELECT MAX(FIELD1) AS F1, MAX(FIELD2) AS F2 FROM ES GROUP BY FIELD3 HAVING F1 = ‘SOME_TEXT’`

Please put attention that F1 is a text field.

The only solution that we found as of now is:

```
{
	"size": 0 ,
	"aggs": {
		"flowId": {
			"terms": {
				"field": "flowId.keyword"
			},
			"aggs" :{
				"scenario" : { "terms" : { "field" : "scnName.keyword" } },
				"max_time" : { "max" : { "field" : "inFlowTimeNsec" } },
				"sales_bucket_filter": {
					"bucket_selector": {
						"buckets_path": {
							"totalSales": "scenario"
						},
						"script": "params.totalSales != null && params.totalSales > 0"
					}
				}
			}
		}
	}
}

```

The issue that we encountered is:

```
{
    "error": {
        "root_cause": [],
        "type": "search_phase_execution_exception",
        "reason": "",
        "phase": "fetch",
        "grouped": true,
        "failed_shards": [],
        "caused_by": {
            "type": "aggregation_execution_exception",
            "reason": "buckets_path must reference either a number value or a single value numeric metric aggregation, got: org.elasticsearch.search.aggregations.bucket.terms.StringTerms"
        }
    },
    "status": 503
}

```

As far as I understand that issue was already raised: [https://github.com/elastic/elasticsearch/issues/23874](https://github.com/elastic/elasticsearch/issues/23874)

The output of the above query without bucket\_selector part looks as following:

```
{
    "took": 52,
    "timed_out": false,
    "_shards": {
        "total": 480,
        "successful": 480,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 15657901,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "flowId": {
            "doc_count_error_upper_bound": 4104,
            "sum_other_doc_count": 9829317,
            "buckets": [
                {
                    "key": "0_66718_31120bfd_39ae_4258_81e8_08abd89a81bf",
                    "doc_count": 107816,
                    "scenario": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "GetPop",
                                "doc_count": 12
                            }
                        ]
                    },
                    "max_time": {
                        "value": 121244876800
                    }
                },
                {
                    "key": "0_67116_31120bfd_39ae_4258_81e8_08abd89a81bf",
                    "doc_count": 107752,
                    "scenario": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "GetPop",
                                "doc_count": 12
                            }
                        ]
                    },
                    "max_time": {
                        "value": 120955101184
                    }
                },
…
}

```

The question is there any other way to achieve what we need?

Thank you a lot,  
EG

---

<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 14, 2018, 1:15pm UTC](https://discuss.elastic.co/t/how-to-get-aggregated-data-by-a-field-from-es-with-two-values-accumulated/128192/2 "2018-05-14T13:15:09Z")

</div>

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