Filtering buckets in aggregation

Elastic Stack Elasticsearch
1

POST assetdefinition_test/_search
{
"size":0,
"aggs":{
"name":{
"terms":{
"field":"AssetType.Name",
"size":0
},
"aggs":{
"avg_price":{
"avg":{
"field":"Price"}
}
}
}
}
}

I have a aggregation query like above where it fetches the "average price" of assets aggregated (bucketed) by name.

But, I need to select only those buckets whose average price is greater than some value (say 100). How to add that filter condition. (similar to 'having' in sql).

2

It is important to point out that this is a problematic query in a distributed system with many unique values for the joining key.

However, given you are talking about asset types as opposed to asset IDs I assume there is a small number of these (< 1000?).

If true then the following should work for you at small scales: https://gist.github.com/markharwood/5f217da5b42525a886b3e405214e9cd7

Doing this sort of aggregation will not scale for high-cardinality fields like asset ID on a distributed system will introduce problems . The solution to that would be to build entity-centric indexes at index time or use multiple queries and term partitioning