# Aggregation: trouble bucketing over a text field value

**URL:** <https://discuss.elastic.co/t/aggregation-trouble-bucketing-over-a-text-field-value/23124>\
**Category:** Elasticsearch\
**Created:** [April 7, 2015, 8:20am UTC](https://discuss.elastic.co/t/aggregation-trouble-bucketing-over-a-text-field-value/23124 "2015-04-07T08:20:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![marko1](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@marko1](https://discuss.elastic.co/u/marko1)\
**Post date:** [April 7, 2015, 8:20am UTC](https://discuss.elastic.co/t/aggregation-trouble-bucketing-over-a-text-field-value/23124/1 "2015-04-07T08:20:32Z")

</div>

Hi,

I'm trying to bucket docs stored in Elasticsearch 1.4 based on nested  
document field values.  
To use an illustrative example, I've an index with documents representing  
companies and their agents like this:

{  
"companyName": "Acme ltd.",  
"country": "us",  
"agents": [{ "name": "McKenzie, Brackman, Chaney and Kuzak" }]  
}

I'd like to count the number of companies that each agent represents and  
order agents by this count.  
In SQL I would do something like this:

SELECT COUNT(\*) cnt FROM agents WHERE country = 'us' AND GROUP BY name  
ORDER BY cnt DESC;

The following ES aggregation query comes close to solving this:

{  
"query": {  
"term": { "country": "us" }  
},  
"aggs": {  
"agents": {  
"nested": {  
"path": "agents"  
},  
"aggs": {  
"agent\_stats": {  
"terms": {  
"field": "agents.name",  
"size": 99999  
}  
}  
}  
}  
}  
}

However, there's a problem that in the above case e.g. separate buckets get  
created for McKenzie and Brackman etc. instead of just one bucket. This is  
probably caused by agents.name field being currently mapped as analyzed.

One solution I've thought of is to copy agents.name field and index it also  
as not\_analyzed.

Is there another way to get this aggregation query to work without index  
mapping changes?  
I'd prefer to not use ES-side scripting for security reasons.

marko

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c113a1fd-1797-4ef9-b281-3f71c9d0245c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c113a1fd-1797-4ef9-b281-3f71c9d0245c%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 12:21am UTC](https://discuss.elastic.co/t/aggregation-trouble-bucketing-over-a-text-field-value/23124/2 "2017-07-06T00:21:15Z")

</div>