Is there any way to get more than one field from an aggregation?

Elastic Stack Elasticsearch
1

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:

 "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?

2

You can add top hit aggregation.

3

I tried but it isn't getting me what I want(I'm most likely using it wrong)
The response currently looks like this:

 {
            "_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:

 {
            "_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
            }
          }
4

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.

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

{
  "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:

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

{
  "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".

5

This worked wonderfully! Thanks!!

1 Like
6

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