如何在Elasticsearch中实现类似PostgreSQL的带条件去重字段计数查询

To replicate the behavior of your PostgreSQL query select count(distinct(candidate_id)) from candidate_ranking cr where badge='1' in Elasticsearch, you'll use a filtered query combined with a cardinality aggregation. Here's the exact query you need:

GET /candidate_ranking/_search
{
  "size": 0,
  "query": {
    "term": {
      "badge": "1"
    }
  },
  "aggs": {
    "distinct_candidate_count": {
      "cardinality": {
        "field": "candidate_id"
      }
    }
  }
}

What each part does: #

• size: 0: We don't need to fetch individual documents, just the aggregation result—this keeps the response lightweight.
• query.term: Filters down to only documents where badge exactly matches '1' (use match instead if badge is an analyzed text field, but your sample data suggests it's a keyword/raw field, so term is the right choice).
• aggs.distinct_candidate_count: The cardinality aggregation calculates the number of unique candidate_id values in the filtered dataset—this is directly equivalent to count(distinct(candidate_id)) in PostgreSQL.

Expected Response for Your Sample Data: #

You'll get a response with the aggregation value set to 2, matching your expected result:

{
  "took": 12,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 4,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "distinct_candidate_count": {
      "value": 2
    }
  }
}

Quick Notes: #

• Make sure candidate_id is mapped as a numeric type (like long or integer) or a keyword type—cardinality works best on non-analyzed fields to avoid accidental value splits.
• For extremely large datasets, cardinality uses a probabilistic HyperLogLog algorithm with a small margin of error. If you need 100% accuracy (and performance allows), you could use a terms aggregation with a sufficiently high size value and count the buckets, but cardinality is far more efficient for big data.

内容的提问来源于stack exchange，提问作者Richesh Chouksey