Elasticsearch Cardinality aggregation with condition

Question

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

Rohit - Please add a) sample document b) mapping c) example of exactly what needs to be fetched. — Sahil Gupta

Sahil Gupta Sahil Gupta · Accepted Answer · 2021-01-06T10:14:48

Try below query (Explanation in comments)

GET items/_search
{
  "size": 0,
  "aggs": {
    "products": {
      "terms": {
        "field": "product_id",
        "size": 10
      },
      "aggs": {
        "seller": {
          "terms": {
            "field": "seller_id",
            "size": 100
          },
          "aggs": {
            "sell_sum": {
              "sum": {
                "field": "sell"
              }
            },
            "sell_bucket_filter": { // <======== pipeline filter
              "bucket_selector": {
                "buckets_path": {
                  "totalSell": "sell_sum"
                },
                "script": {
                  "source": "params.totalSell > 100"
                }
              }
            }
          }
        },
        "seller_counts": { // <======== pipeline filter
          "stats_bucket": {
            "buckets_path": "seller>sell_sum"
          }
        }
      }
    }
  }
}

Please note:

Sub-aggregation are not allowed under cardinality aggregation i.e why your query is not working
bucket_selector & stats_bucket both are pipeline aggregations and works on the output of another aggregation.
seller_counts will return incorrect results if the seller->size is set to a value lower than the actual count, as the aggregation is a pipeline aggregation

Elasticsearch Cardinality aggregation with condition

1 Answers