29

How can I add a new key called 'agency_name' in my output bucket.

I am running an aggregation code as shown below

{
  "aggs": {
    "name": {
      "terms": {
        "field": "agency_code"
      }
    }
  }
}

I will be getting the out put as

"aggregations": {
    "name": {
        "doc_count_error_upper_bound": 130,
        "sum_other_doc_count": 39921,
        "buckets": [
            {
                "key": "1000",
                "doc_count": 105163
            },
            {
                "key": "2100",
                "doc_count": 43006
            }
        ]
    }
}

While displaying I need to show the agency name, code and doc_count

How can I modify the aggregation query so that I could get the below format. I am new to ElasticSearch, not sure how to fix this 

"aggregations": {
    "name": {
        "doc_count_error_upper_bound": 130,
        "sum_other_doc_count": 39921,
        "buckets": [
            {
                "key": "1000",
                "doc_count": 105163,
                "agency_name": 'Agent 1'
            },
            {
                "key": "2100",
                "doc_count": 43006,
                "agency_name": 'Agent 2'
            }
        ]
    }
}

Sample Data in ElasticSearch (fields are analysed)

{

    "_index": "feeds",
    "_type": "news",
    "_id": "22005",
    "_version": 1,
    "_score": 1,
    "_source": {
        "id": 22005,
        "name": "Test News",
        "agency_name": "Agent 1",
        "agency_code": "1000",
    }

}
Sumit
  • 2,190
  • 23
  • 31
Amal Kumar S
  • 15,555
  • 19
  • 56
  • 88

5 Answers5

20

You can use the top hits aggregation like in the link below. The format will be slightly different since creating the extra aggregation will embed the agency name under another 'hits' key.

Adding additional fields to ElasticSearch terms aggregation

{
  "aggs": {
    "name": {
      "terms": {
        "field": "agency_code"
      },
      "aggs": {
        "agency_names" : {
           "top_hits": {
                size: 1, 
                _source: {
                    include: ['agency_name']
                }
            }
         } 
       }
    }
  }
}
Community
  • 1
  • 1
Rajas Agashe
  • 371
  • 2
  • 5
  • This solution works great. Just one question, does it make any difference on performance if we use `docvalue_fields` instead of `_source` in the above query? I'm aware that they are 2 different things, just ask in term of performance – Hoàng Long Feb 04 '21 at 04:18
2

I think you would need to add another "aggs" to it. But it would not be in the format in which you want but as another field in the output , reason being currently you are aggregating based on "agency_code" and the doc_count shows how many times the particular agency code occurs. Now when you want to aggregate it based on "agency_name" the field might in different documents than "agency_code" and in different numbers as well , if they always exist in pair than this parent-child indexing might be of some help.

https://www.elastic.co/guide/en/elasticsearch/guide/current/indexing-parent-child.html

Aditya Patel
  • 569
  • 1
  • 10
  • 28
  • looks like I don't need to aggregate based on agency_name. What I was planning is, any way I could add custom fields to an aggregation result. I have added a sample record to show how data is kept in elasticsearch. – Amal Kumar S Jul 30 '15 at 12:08
  • Hi @AmalKumarS May I know how did you solve this problem. – Kailash Oct 03 '17 at 11:19
  • 1
    @Kailash I was not able to get the agency_name from aggregation result. What I did is I ran a seperate query to have agency code and agency name mapped. Which displaying the aggregated result, it used this mapping to display the agency name :( – Amal Kumar S Oct 12 '17 at 08:02
1

ES has no way of knowing agency_name and agency_code map one-to-one. Therefore I would recommend a number of possible strategies.

  • Don't analyze agency_name and use the term agg over that field. I would be surprised if you actually need to do tokenization of the agency_name.
  • Store the id to name mapping in a relational database or a flat file cache and do the join client side
  • Store the agency documents as another type and make two calls. The first to get the ids and then a second to lookup the agencies by id

As Aditya Patel mentioned above, parent child relationships may help out as well but I believe you will still have to use one of the above strategies to resolve the id->name mapping.

Andrew White
  • 52,720
  • 19
  • 113
  • 137
1

This is old post, however, I ran into same issue and I followed what is given at https://www.elastic.co/guide/en/elasticsearch/reference/current/agg-metadata.html. Add metadata details and it is return as part of the result above bucket. Hope it will help someone in future.

Vishwa
  • 31
  • 4
0

What I do is use something like the following query: 

"aggs" : {
    "products" : {
      "filter" : { "term": { "item.category": "children" }},
      "aggs" : {
        "count" : {
          "terms" : {
            "script": "doc['item.id'].value + ':' + doc['item.name'].value"
          }
        }
      }
    }
  }

Which returns something like this:

...
"aggregations" : {
    "products" : {
      "doc_count" : 1050,
      "count" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "x2_90QBj9k:Baby Oil",
            "doc_count" : 45
          },
          ...
        ]
...

And then I can use a string operation on bucket[i]["key"], for each i in a loop, to extract the relevant field.

Chukwuma Nwaugha
  • 575
  • 8
  • 17