aggregations query: How do i aggregate and get back multiple values with document count?

Question

I have multiple elastic documents, with fields "cityId", "cityName". I want to count occurrences of documents with specific "cityId" and "cityName".

Aggregation query to fetch cityId and count:

GET _search
{
  "aggs": {
    "CityIdCount": {
      "terms": {
        "field": "cityId",
        "size": 0
      }
    }
  },
  "query": {
    "bool": {
      "must": [
...........................

This is the existing query and it gives the result as (key as cityId and doc_count as number of occurrences: { "key": 40, "doc_count": 4906 },

This is query i tried to fetch (cityId,cityName,doc_count) and it gives the result as (key as cityName and doc_count as number of occurrences of that name: { "key": "Thane", "doc_count": 4906 }

GET _search
{
  "aggs": {
    "CityIdCount": {
      "terms": {
        "field": "cityId",
        "field": "cityName",
        "size": 0
      }
    }
  },
  "query": {
    "bool": {
      "must": [
   ..............................

I want all together, example: "cityId","cityName","doc_count".

What would be the correct query for that?

Russ Cam Russ Cam · Accepted Answer · 2016-06-12T01:15:06

You can't (currently at least) perform a terms aggregation across a conjunction of multiple fields in the way that you're suggesting. You can do it in one of two ways:

Use a terms aggregation with a script that concatenates the fields on which you want to perform the aggregation. This may perform well enough for your use case, but be aware that you are paying a performance cost at query time.
Combine the field values on which you want to perform the aggregation at index time into a new not_analyzed field e.g. cityIdAndName either in your application or using copy_to and use the terms aggregation on this field. This will perform much better than the first option at the cost of indexing more data.

aggregations query: How do i aggregate and get back multiple values with document count?

1 Answers