I want to do cardinality aggregation with condition.
I want to count all the unique sellers whose sell sum should be greater than 100 for product list.
Is there any way to do this using cardinality or anything else?
I have tried with Bucket selector and Cardinality following query but it did not worked.
Mappings
{
"mappings": {
"properties": {
"product_id": {
"type": "long"
},
"seller_id": {
"type": "long"
},
"sell": {
"type": "double"
}
}
}
}
Sample Documents
[
{
"product_id": 1,
"seller_id": 1,
"sell": 70
},
{
"product_id": 1,
"seller_id": 1,
"sell": 40
},
{
"product_id": 1,
"seller_id": 2,
"sell": 10
},
{
"product_id": 2,
"seller_id": 1,
"sell": 20
},
{
"product_id": 2,
"seller_id": 2,
"sell": 120
},
{
"product_id": 2,
"seller_id": 3,
"sell": 90
},
{
"product_id": 2,
"seller_id": 3,
"sell": 20
}
]
Query
{
"size": 0,
"aggregations": {
"products": {
"terms": {
"field": "product_id"
},
"aggregations": {
"seller_count": {
"cardinality": {
"field": "seller_id"
},
"aggregations": {
"total_sell": {
"sum": {
"field": "sell"
}
},
"sell_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"totalSell": "total_sell"
},
"script": {
"source": "params.totalSell > 100"
}
}
}
}
}
}
}
}
}
Expected Sample Result
{
"aggregations": {
"products": {
"buckets": [
{
"key": 1, // product_id
"seller_count": {
"value": 1 // 1 Seller is present whose sell sum is greater than 100
}
},
{
"key": 2, // product_id
"seller_count": {
"value": 2 // 2 Seller is present whose sell sum is greater than 100
}
}
]
}
}
}