Elasticsearch Cardinality aggregation with condition

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
                    }
                }
            ]
        }
    }
}

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