# Counting buckets with a particular value **URL:** \ **Category:** Elasticsearch\ **Created:** [June 20, 2018, 10:53am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671 "2018-06-20T10:53:39Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![m\_arku\_s](https://avatars.discourse-cdn.com/v4/letter/m/ebca7d/32.png) [@m\_arku\_s](https://discuss.elastic.co/u/m_arku_s)\ **Post date:** [June 20, 2018, 10:53am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671/1 "2018-06-20T10:53:39Z")
Using Elasticsearch 6.2.x, I am failing to write a particular query. My data contains information about when an actor commenced one of several consecutive phases: ``` curl -XPUT http://localhost:9200/phases curl -XPUT http://localhost:9200/phases/_mapping/_doc -H'Content-Type:application/json' -d'{ "properties": { "actor": { "type": "keyword" }, "verb": { "type": "keyword" }, "object": { "type": "keyword" } } }' curl -XPOST http://localhost:9200/phases/_doc -H'Content-Type:application/json' -d'{ "actor": "actor1", "verb": "started", "object": "phase2", "timestamp": "2018-06-20T12:00:00.0000Z" }' curl -XPOST http://localhost:9200/phases/_doc -H'Content-Type:application/json' -d'{ "actor": "actor1", "verb": "started", "object": "phase1", "timestamp": "2018-06-18T12:00:00.0000Z" }' curl -XPOST http://localhost:9200/phases/_doc -H'Content-Type:application/json' -d'{ "actor": "actor2", "verb": "started", "object": "phase1", "timestamp": "2018-06-19T12:00:00.0000Z" }' ``` What I'm trying to find out is how many actors are presently in which phase, where only the most recently started phase is considered for each actor (phase1:1, phase2:1 in the example). I know how to query for the phase each actor is presently in by combining a terms with a top\_hits query as follows: ``` curl -XPOST http://localhost:9200/phases/_search -H'Content-Type:application/json' -d'{ "aggs": { "_xs_valueagg": { "terms": { "field": "actor" }, "aggs": { "latest": { "top_hits": { "size": 1, "sort": [ { "timestamp": { "order": "desc" } } ], "_source": { "includes": ["object"] } } } } } } }' ``` However, this gives results of the general form: actor1:phase2, actor2:phase1, etc. What I'm failing to achieve is to count the number of actors per phase across these buckets. Since this is a post-processing step on existing buckets I have attempted various forms of pipeline aggregations (stats and bucket\_script in particular) but fail to (a) create an entirely new bucket structure and (b) count the occurrences of a categorical attribute. Is there a way to query for the number of actors per phase, where only the most recently entered phase is considered per actor? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [June 21, 2018, 8:56am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671/2 "2018-06-21T08:56:02Z")
If you have a lot of actors and shards this is computationally hard - especially if you also use time-based indices and default routing of data to shards. It might be worth considering [entity centric indexing](https://twitter.com/elasticmark/status/1009380268409610240) - maintaining a doc per actor or actor-session. The benefits are that as well as the `actor_current_phase` attribute you want you could also have attributes like `actor_phase1_duration` and `actor_phase2_duration` or `actor_has_invalid_phase_order` etc ready for fast analysis in Kibana ---
**Author:** ![m\_arku\_s](https://avatars.discourse-cdn.com/v4/letter/m/ebca7d/32.png) [@m\_arku\_s](https://discuss.elastic.co/u/m_arku_s)\ **Post date:** [June 22, 2018, 8:22am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671/3 "2018-06-22T08:22:28Z")
Thanks Mark, I had a feeling I needed to re-structure my data to support this query. Interesting to learn that there's a name for this type of structure and tools to support it. Thank you for making the re-indexing scripts available! Just out of curiosity: is it correct that, given the current organization of the data, it's not even feasible to express this type of query using the query DSL (ignoring computational cost for a moment)? My reasoning being that I've "used up" my one level of bucketing for the TOP 1 part, and that pipeline aggregations can't express the type of bucket restructuring I need. Or am I missing a construct in the query DSL that would allow me to do just that. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [June 22, 2018, 8:45am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671/4 "2018-06-22T08:45:20Z")
For complex cases there is always the [scripted aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/6.3/search-aggregations-metrics-scripted-metric-aggregation.html) but with an event-centric index and your sort of business question, there's always going to be a scalability limit to the number of entities you can consider in a single aggregation request. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 20, 2018, 8:45am UTC](https://discuss.elastic.co/t/counting-buckets-with-a-particular-value/136671/5 "2018-07-20T08:45:21Z")
Closed on July 20, 2018, 8:45am UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.