# Elasticsearch Apply filters with results from aggregations
**URL:** https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744
**Category:** Elasticsearch
**Created:** [July 3, 2022, 10:45am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744 "2022-07-03T10:45:00Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Eddie\_Vuong](https://discuss.elastic.co/u/Eddie_Vuong)
#### Post date: [July 3, 2022, 10:45am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/1 "2022-07-03T10:45:01Z")
Is there anyway I can using the results obtained from aggregations to filter out the final hits in the query?
I want to obtain **a list of users who have more than 2 devices and the list of their devices** in the database. The device count can be done using `aggregations`, however I'm having a hard time trying to figure out how to use that results to apply on the final hits.
I thought about using `post_filter` but it didn't seem to work.
Here my code
```auto
{
"query": {
"bool": {
"filter": dt_filter,
"must": {"term": {"username": "cakenoodle"}}
}
},
"aggs": {
"user_count": {
"aggs": {
"nb_device": {"cardinality": {"field": "device_uuid"}},
"nb_ip_addr": {"cardinality": {"field": "ip.address"}},
"sus_count": {
"bucket_selector": {
"buckets_path": {
"nb_device": "nb_device",
"nb_ip_addr": "nb_ip_addr",
},
"script": "params.nb_device >= 2 && params.nb_ip_addr >= 2"
}
}
},
"terms": {
"field": "username"
}
},
},
"post_filter": {"range": {"nb_device": {"gte": 2}}} // didn't work here
}
```
The equivalence of SQL would be something like this:
```sql
WITH
device_count AS (
SELECT
user,
COUNT(device_id) nb_device
FROM table
GROUP BY user
HAVING COUNT(device_id) >= 2
)
SELECT
table.user,
table.device
FROM table
JOIN device_count ON device_count.user = table.user
```
---
### Author:  [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)
#### Post date: [July 3, 2022, 3:33pm UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/2 "2022-07-03T15:33:41Z")
To obtain list of devices, add terms aggregation on devices as sub-aggregation for `username` terms aggregation.
Something like this:
```auto
GET kibana_sample_data_flights/_search
{
"size":0,
"aggs":{
"city":{
"terms":{
"field": "DestCityName",
"size": 10000
},
"aggs":{
"airports":{
"terms":{
"field": "DestAirportID"
}
},
"nb_airport": {
"cardinality": {
"field": "DestAirportID"
}
},
"nb_airport_filter":{
"bucket_selector":{
"buckets_path":{
"nb_airport": "nb_airport"
},
"script": "params.nb_airport >= 2"
}
},
"sort":{
"bucket_sort": {
"sort": [
{"nb_airport":{"order":"desc"}}
]
}
}
}
}
}
}
```
You will get:
```auto
{
"aggregations" : {
"city" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "London",
"doc_count" : 329,
"nb_airport" : {
"value" : 3
},
"airports" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "LTN",
"doc_count" : 130
},
{
"key" : "LGW",
"doc_count" : 111
},
{
"key" : "LHR",
"doc_count" : 88
}
]
}
},
{
"key" : "Rome",
"doc_count" : 191,
"nb_airport" : {
"value" : 3
},
"airports" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "FCO",
"doc_count" : 89
},
{
"key" : "RM11",
"doc_count" : 70
},
{
"key" : "RM12",
"doc_count" : 32
}
]
}
},......
```
The result contains DestAirportID for each DestCityName matching the condition of `bucket_selector`.
---
### Author:  [@Eddie\_Vuong](https://discuss.elastic.co/u/Eddie_Vuong)
#### Post date: [July 4, 2022, 2:28pm UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/3 "2022-07-04T14:28:28Z")
> [@Tomo\_M](#):
>
> `000`
Thanks for your support. I just have 2 more following questions regarding this:
1. When I set the `size = 10000`, an error `TransportError: TransportError(503, 'search_phase_execution_exception')` was raised. The maximum size I could use was `2000`. How can I increase the size?
2. Is there a way to know how many aggregated results will be returned? How can I paginate if I have more than `10000` (or `2000` in my case) in `size`?
Searching Google hasn't given me any satisfying answers yet...
---
### Author:  [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)
#### Post date: [July 4, 2022, 2:43pm UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/4 "2022-07-04T14:43:56Z")
See [Size](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-size) section in the terms aggregation doc. The default value of `search.max_buckets` is 65,536. Take care that someone set the value to 2,000 with some intention.
As described in the doc, you can use [composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_pagination) to pagenate on the aggregation result.
---
### Author:  [@Eddie\_Vuong](https://discuss.elastic.co/u/Eddie_Vuong)
#### Post date: [July 4, 2022, 3:19pm UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/5 "2022-07-04T15:19:03Z")
Actually, it also happened with the same error message when I set the size to `1000`. Not sure what caused the error since it seemed to be okay before.
---
### Author:  [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)
#### Post date: [July 5, 2022, 1:16am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/6 "2022-07-05T01:16:59Z")
I need full query and error message to consider about the reason.
---
### Author:  [@Eddie\_Vuong](https://discuss.elastic.co/u/Eddie_Vuong)
#### Post date: [July 5, 2022, 2:51am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/7 "2022-07-05T02:51:19Z")
Here my code:
```auto
"aggregations": {
"unique_users": {
"terms": {
"field": "username",
"size": 500
},
"aggs": {
"device_uuid": {"terms": {"field": "device_uuid"}},
"ip_address": {"terms": {"field": "ip.address"}},
"nb_device": {"cardinality": {"field": "device_uuid"}},
"nb_ip_add": {"cardinality": {"field": "ip.address"}},
"nb_device_filter": {
"bucket_selector": {
"buckets_path": {
"nb_device": "nb_device",
"nb_ip_add": "nb_ip_add"
},
"script": "params.nb_device >= 2 && params.nb_ip_add >= 2"
}
}
}
}
}
```
This is the error message: `TransportError: TransportError(503, 'search_phase_execution_exception')`
I experimented using different `size`, now none seems to work.
---
### Author:  [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)
#### Post date: [July 5, 2022, 8:53am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/8 "2022-07-05T08:53:37Z")
Is that the entire error message?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 2, 2022, 8:54am UTC](https://discuss.elastic.co/t/elasticsearch-apply-filters-with-results-from-aggregations/308744/9 "2022-08-02T08:54:24Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.