# Is there any way to get more than one field from an aggregation? **URL:** \ **Category:** Elasticsearch\ **Created:** [January 18, 2022, 9:36pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741 "2022-01-18T21:36:15Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Maxi\_Donadio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxi_donadio/32/97686_2.png) [@Maxi\_Donadio](https://discuss.elastic.co/u/Maxi_Donadio)\ **Post date:** [January 18, 2022, 9:36pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/1 "2022-01-18T21:36:15Z")
Hi! I need to know if it's possible to get more than one value from an aggregation. If I were to write my desired query as SQL it'd be something like: SELECT value1, value2, value3 FROM table WHERE condition. I'd like to get more than just a 'key' value. My aggregation currently looks like this: ```auto "aggs": { "by_host_ip": { "terms": { "field": "host.ip", "size": 9999}, "aggs": { "avg_utilization": { "avg": { "field": "system.memory.actual.used.pct" } } } } } ``` I need to add the hostname to this. Is there any way to do this? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 18, 2022, 11:32pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/2 "2022-01-18T23:32:04Z")
You can add top hit aggregation. > **[Top hits aggregation | Elasticsearch Guide \[7.16\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html)** ---
**Author:** ![Maxi\_Donadio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxi_donadio/32/97686_2.png) [@Maxi\_Donadio](https://discuss.elastic.co/u/Maxi_Donadio)\ **Post date:** [January 19, 2022, 2:24pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/3 "2022-01-19T14:24:37Z")
I tried but it isn't getting me what I want(I'm most likely using it wrong) The response currently looks like this: ```auto { "_index" : "(Private info, not gonna post it here)", "_type" : "_doc", "_id" : "msji_X0BXM8tk6j7vI8f", "_score" : 1.0581832, "_source" : { "agent" : { "name" : "Private info, not gonna post it here" }, "host" : { "ip" : [ "This is an IP", "Private info, not gonna post it here" ] } } } ``` I want it to have the average system memory usage as well, something like: ```auto { "_index" : "(Private info, not gonna post it here)", "_type" : "_doc", "_id" : "msji_X0BXM8tk6j7vI8f", "_score" : 1.0581832, "_source" : { "agent" : { "name" : "Private info, not gonna post it here" }, "host" : { "ip" : [ "This is an IP", "Private info, not gonna post it here" ] }, "avg_utilization":123456789 } } ``` ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 19, 2022, 2:45pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/4 "2022-01-19T14:45:27Z")
top\_hit aggregation returns some document sorted by specified order. You can get `host.name` from one (or some) representative document of that bucket. sample on kibana\_sample\_data\_flights. ```auto GET /kibana_sample_data_flights/_search { "size":0, "aggs": { "by_host_ip": { "terms": { "field": "DestAirportID", "size": 1}, "aggs": { "avg_utilization": { "avg": { "field": "AvgTicketPrice" } }, "name":{ "top_hits":{ "size":1, "_source": ["DestCityName"] } } } } } } ``` ```auto { "took" : 10, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 10000, "relation" : "gte" }, "max_score" : null, "hits" : [] }, "aggregations" : { "by_host_ip" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 12368, "buckets" : [ { "key" : "ZRH", "doc_count" : 691, "name" : { "hits" : { "total" : { "value" : 691, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "kibana_sample_data_flights", "_type" : "_doc", "_id" : "Tpr_Zn4Bf0nakUP8OrRZ", "_score" : 1.0, "_source" : { "DestCityName" : "Zurich" } } ] } }, "avg_utilization" : { "value" : 575.1067587028537 } } ] } } } ``` Another option using terms aggregation: ```auto GET /kibana_sample_data_flights/_search { "size":0, "aggs": { "by_host_ip": { "terms": { "field": "DestAirportID", "size": 1}, "aggs": { "avg_utilization": { "avg": { "field": "AvgTicketPrice" } }, "name":{ "terms": { "field": "DestCityName", "size": 10 } } } } } } ``` ```auto { "took" : 42, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 10000, "relation" : "gte" }, "max_score" : null, "hits" : [] }, "aggregations" : { "by_host_ip" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 12368, "buckets" : [ { "key" : "ZRH", "doc_count" : 691, "name" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "Zurich", "doc_count" : 691 } ] }, "avg_utilization" : { "value" : 575.1067587028537 } } ] } } } ``` Both returns DestCityName: "Zurich" which is identital to the DestAirportID "ZRH". ---
**Author:** ![Maxi\_Donadio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxi_donadio/32/97686_2.png) [@Maxi\_Donadio](https://discuss.elastic.co/u/Maxi_Donadio)\ **Post date:** [January 19, 2022, 2:56pm UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/5 "2022-01-19T14:56:01Z")
> [@Tomo\_M](#): > > ```auto > "aggs": { > "by_host_ip": { > "terms": { "field": "DestAirportID", "size": 1}, > "aggs": { > "avg_utilization": { > "avg": { "field": "AvgTicketPrice" } > }, > "name":{ > "terms": { > "field": "DestCityName", > "size": 10 > } > } > } > } > } > > ``` This worked wonderfully! Thanks!! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 9, 2022, 10:56am UTC](https://discuss.elastic.co/t/is-there-any-way-to-get-more-than-one-field-from-an-aggregation/294741/6 "2022-02-09T10:56:14Z")
This topic was automatically closed 20 days after the last reply. New replies are no longer allowed.