# Filtering collection in aggregation based on document property value

**URL:** <https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592>\
**Category:** Elasticsearch\
**Created:** [March 14, 2022, 9:01am UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592 "2022-03-14T09:01:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mahendra\_Choudhary](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mahendra_choudhary/32/102984_2.png) [@Mahendra\_Choudhary](https://discuss.elastic.co/u/Mahendra_Choudhary)\
**Post date:** [March 14, 2022, 9:01am UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592/1 "2022-03-14T09:01:21Z")

</div>

Hello all,

I am kind of stuck in a special case.  
My document looks like below.  
{  
"\_source" : {  
"name" : "SPIM",  
"spDetails" : {  
"isActive" : true  
},  
"id" : "251561",  
"categories" : [  
"FM",  
"SP"  
]  
}  
},  
{  
"\_source" : {  
"name" : "WPC",  
"spDetails" : {  
"isActive" : false  
},  
"id" : "112283",  
"categories" : [  
"SP",  
"FM"  
]  
}  
},  
{  
"\_source" : {  
"name" : "AVG",  
"spDetails" : {  
"isActive" : false  
},  
"id" : "184612",  
"categories" : [  
"SP",  
"FM"  
]  
}  
},  
{  
"\_source" : {  
"name" : "ACP",  
"spDetails" : {  
"isActive" : false  
},  
"id" : "339335",  
"categories" : [  
"FM",  
"SP"  
]  
}  
},  
{  
"\_source" : {  
"name" : "GIC",  
"spDetails" : {  
"isActive" : false  
},  
"id" : "390040",  
"categories" : [  
"FM",  
"SP"  
]  
}  
}

I want to aggregate categories only if "spDetails.isActive" is true.  
Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Scrilling](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/scrilling/32/78663_2.png) [@Scrilling](https://discuss.elastic.co/u/Scrilling)\
**Post date:** [March 17, 2022, 1:20am UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592/2 "2022-03-17T01:20:18Z")

</div>

Hi Mahendra,

What kind of aggregation are you looking to perform? Regardless, you can include a query _and_ an aggregation in a single request to the Search API, which sounds like what you might be after. The resulting aggregation will only compute across search results that match the query criteria.  
For example,

```auto
GET my_index/_search
{
  "query": {
    "term": {
      "spDetails.isActive": true
    }
  },
  "aggs": {
    "top_categories": {
      "terms": {
        "field": "categories"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Mahendra\_Choudhary](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mahendra_choudhary/32/102984_2.png) [@Mahendra\_Choudhary](https://discuss.elastic.co/u/Mahendra_Choudhary)\
**Post date:** [March 17, 2022, 1:51am UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592/3 "2022-03-17T01:51:18Z")

</div>

Hello @Scrilling ,

Thanks for your reply.  
Let's assume my data is below.

```auto
{
    "_source" : {
        "name" : "WPC",
        "spDetails" : {
            "isActive" : false
         },
       "id" : "112283",
       "categories" : [
           "SP",
           "FM"
        ]
}
},

```

I want aggregation on categories but SP should not considered from categories if spDetails.isActive is false.

From above data categories collection should contain only "FM".  
This is just example we have several categories on each record.

Unfortunately I can't put query on spDetails.isActive field. Query is performed on other fields, which is not included in this simple data.

---

<div class="post-metadata">

**Author:** ![Scrilling](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/scrilling/32/78663_2.png) [@Scrilling](https://discuss.elastic.co/u/Scrilling)\
**Post date:** [March 22, 2022, 9:32pm UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592/4 "2022-03-22T21:32:14Z")

</div>

Hi Mahendra,

If SP is the only category with a need for a filter, you could split your aggregation into two buckets for `spDetails.isActive: true` and `spDetails.isActive: false` using a filter or terms agg. From there, you could aggregate categories in each bucket and use something like a [bucket selector](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html) agg for the `false` bucket to filter out `SP` results.

This obviously gets unwieldy pretty quickly if you have multiple `isActive` filters for each category. In that case, I would consider if it would make sense to model your `categories` as a [nested field](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html#nested-fields-array-objects) with `category` and `isActive` properties. With this structure you could aggregate across all of your categories within the context of a [nested aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html) that [filters](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html) on `categories.isActive`. Nested queries can be computationally expensive, so it ultimately comes down to a cost/benefit analysis.

---

<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:** [April 19, 2022, 9:33pm UTC](https://discuss.elastic.co/t/filtering-collection-in-aggregation-based-on-document-property-value/299592/5 "2022-04-19T21:33:03Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.