Elasticsearch：筛选生日未录入或早于1963/3/1的唯一姓名方法

Hey there! I see you're trying to filter employees who either don't have a birthday recorded or were born before March 1st, 1963, and you want each name to appear only once in your results. Let's break down why your initial approach didn't work and fix it.

Why Your Terms Aggregation Didn't Give Unique Results #

The terms aggregation you added is great for counting how many times each name appears, but it doesn't modify the hits section of your response. That's why you still saw duplicate names in your results—you were just getting a side count of duplicates, not deduplicated documents.

Solution 1: Use collapse to Deduplicate Documents #

If you want to get full employee documents with unique names, Elasticsearch's collapse feature is exactly what you need. It groups results by a specified field (in your case, name) and returns only the first matching document for each group.

Here's the adjusted query with collapse:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must_not": [
              { "exists": { "field": "birthday" } }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              { "range": { "birthday": { "lte": "19630301" } } }
            ]
          }
        }
      ]
    }
  },
  "collapse": {
    "field": "name"
  },
  "from": 0,
  "size": 25
}

Since your name field is mapped as a keyword, this will perfectly match exact names and deduplicate them. The hits section will now have one document per unique name that matches your filter criteria.

Bonus: See Duplicate Documents for Each Name #

If you also want to see how many duplicates exist for each name (and view them), add an inner_hits section to the collapse:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must_not": [
              { "exists": { "field": "birthday" } }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              { "range": { "birthday": { "lte": "19630301" } } }
            ]
          }
        }
      ]
    }
  },
  "collapse": {
    "field": "name",
    "inner_hits": {
      "name": "duplicate_records",
      "size": 10
    }
  },
  "from": 0,
  "size": 25
}

This will attach a duplicate_records section to each collapsed result, showing up to 10 duplicate documents for that name.

Solution 2: Get Only Unique Names (No Full Documents) #

If you just need a list of unique names (not the full employee data), you can tweak your terms aggregation to return all unique names by setting a large enough size value, and disable the hits section with "size": 0:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must_not": [
              { "exists": { "field": "birthday" } }
            ]
          }
        },
        {
          "bool": {
            "filter": [
              { "range": { "birthday": { "lte": "19630301" } } }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "unique_names": {
      "terms": {
        "field": "name",
        "size": 10000  // Adjust this to cover the total number of unique names you expect
      }
    }
  },
  "size": 0
}

You'll find all unique names in aggregations.unique_names.buckets—each bucket's key is the name, and doc_count is how many times it appears in the filtered results.

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