How to get other fields along with the term aggregation in Elasticsearch?

Question

I am using Elasticsearch 5.3. I want to use count a field and then group by another field. On top of this, I wish to get the other fields in that document also.

"aggs" : {
  "people" : {
    "terms" : { "field" : "name" },
    "aggs" : {
      "event_count" : { "value_count" : { "field" : "event" } }
    }
  }
}

The above ES query is equivalent to SELECT COUNT(event) FROM table GROUP BY name;. The new ES query should be like this SQL query: SELECT name, address, age, COUNT(event) FROM table GROUP BY name;. How do I do this?

Answer 1

One way you can achieve this is by using the top_hits aggregation:

{
  "aggs": {
    "plans": {
      "people": {
        "field": "name"
      },
      "aggs": {
        "docs": {
          "top_hits": {
            "size": 1,
            "_source": [ "address", "age" ]
          }
        },
        "event_count": {
          "value_count": {
            "field": "event"
          }
        }
      }
    }
  }
}

You need to realize that in your SQL query you should also group by age and address otherwise you will "miss" some of their values if you only group by name. Try it out and you'll see the difference.