# Restrict buckets on sub-aggregate counts?

**URL:** <https://discuss.elastic.co/t/restrict-buckets-on-sub-aggregate-counts/52792>\
**Category:** Elasticsearch\
**Created:** [June 15, 2016, 12:19am UTC](https://discuss.elastic.co/t/restrict-buckets-on-sub-aggregate-counts/52792 "2016-06-15T00:19:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![photonic\_world\_2](https://avatars.discourse-cdn.com/v4/letter/p/e5b9ba/32.png) [@photonic\_world\_2](https://discuss.elastic.co/u/photonic_world_2)\
**Post date:** [June 15, 2016, 12:19am UTC](https://discuss.elastic.co/t/restrict-buckets-on-sub-aggregate-counts/52792/1 "2016-06-15T00:19:49Z")

</div>

Hello,

Here is a query which I am trying:

I have a bunch of users as a string array in each of my document.

> {  
> "users":["user1", "user2"]  
> "access":"permitted"  
> }

document 2

> {  
> "users":["user1", "user3"]  
> "access":"blocked"  
> }

document 3

> {  
> "users":["user5", "user6"]  
> "access":"blocked"  
> }

I want to see if its possible to aggregate by users while getting counts for say 'permitted' users only  
so the response would be something like:

> {  
> "aggregations":{  
> "buckets": [  
> {  
> "key": "user1",  
> "doc\_count": 2,  
> "access": {  
> "doc\_count\_error\_upper\_bound": 0,  
> "sum\_other\_doc\_count": 0,  
> "buckets": [  
> {  
> "key": "permitted",  
> "doc\_count": 1  
> },  
> {  
> "key": "blocked",  
> "doc\_count": 1  
> }  
> ]  
> }  
> }  
> }  
> }

Query like below could help but global scope can not be set on the sub aggregates

> {  
> "size": 0,  
> "query": {  
> "constant\_score": {  
> "filter": {  
> "term": {  
> "access": "permitted"  
> }  
> }  
> }  
> },  
> "aggregations": {  
> "by\_users": {  
> "terms": {  
> "field": "users",  
> "size": 10,  
> "shard\_size": 0  
> },  
> "aggs": {  
> "access": {  
> "global": {},  
> "aggs":{  
> "by\_access":{  
> "terms": {  
> "field": "access"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }

I can get this with two queries one to fetch all the unique users and then fetch aggregate by access over those users, the second query would be huge as number of users there would be huge (say in thousands and millions)

Is there a better way to do this?

---

<div class="post-metadata">

**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 15, 2016, 11:17am UTC](https://discuss.elastic.co/t/restrict-buckets-on-sub-aggregate-counts/52792/2 "2016-06-15T11:17:29Z")

</div>

> [@photonic\_world\_2](#):
>
> Is there a better way to do this?

Your scenario ticks all the boxes for creating a "bucket explosion" problem [1].  
Consider creating a user-centric index for this type of analysis.

[1] [https://speakerdeck.com/elastic/building-entity-centric-indexes?slide=5](https://speakerdeck.com/elastic/building-entity-centric-indexes?slide=5)

---

<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 5, 2017, 10:43pm UTC](https://discuss.elastic.co/t/restrict-buckets-on-sub-aggregate-counts/52792/3 "2017-07-05T22:43:43Z")

</div>