Elasticsearch：对应指定SQL查询的Term聚合实现方法咨询

Got it, let's break this down exactly how you need it. Your SQL logic translates cleanly to Elasticsearch's query + aggregation system—here's a step-by-step breakdown with working examples:

First, map your SQL concepts to Elasticsearch:

• WHERE fieldB LIKE '%value%' = a filter query to narrow down matching documents
• SELECT DISTINCT fieldA = a terms aggregation to fetch unique values of fieldA

Step 1: Filter Documents Matching fieldB LIKE '%value%' #

You have two solid options for the filter, depending on your field mapping:

• Wildcard query: Exact match for substrings (just like SQL's LIKE '%value%'). Ideal if fieldB is a keyword field, or you need literal substring matching.
• Match query: Better for text fields (tokenized), as it will match documents where any token contains your target value (tweak with operator: "and" if you need stricter matches).

Step 2: Aggregate Unique fieldA Values #

Use the terms aggregation to pull distinct fieldA values. A few critical notes here:

• If fieldA is a text field, use its keyword sub-field (e.g., fieldA.keyword)—terms aggregations don't play well with tokenized text.
• Set a sufficient size to retrieve all unique values (default is only 10, which is almost never enough for distinct use cases). For massive datasets, use composite aggregation for pagination (more on that below).

Example 1: Basic Terms Aggregation (Small/Medium Datasets) #

GET /your_index_name/_search
{
  "size": 0, // Skip returning raw documents—we only care about aggregation results
  "query": {
    "wildcard": {
      "fieldB": "*value*" // Replace "value" with your target substring
    }
  },
  "aggs": {
    "distinct_fieldA": { // Custom name for your aggregation result
      "terms": {
        "field": "fieldA.keyword", // Use "fieldA" directly if it's a keyword-type field
        "size": 10000 // Adjust based on how many unique values you expect
      }
    }
  }
}

Example 2: Composite Aggregation (Large Datasets) #

If you have thousands of unique fieldA values, a huge size in terms aggregation can kill performance. Use composite to paginate results instead:

GET /your_index_name/_search
{
  "size": 0,
  "query": {
    "wildcard": {
      "fieldB": "*value*"
    }
  },
  "aggs": {
    "paginated_distinct_fieldA": {
      "composite": {
        "size": 1000,
        "sources": [
          { "fieldA": { "terms": { "field": "fieldA.keyword" } } }
        ]
      }
    }
  }
}

To get the next page of results, add an after parameter to the request using the key from the last bucket in your previous response.

Quick Best Practices #

• size: 0 is non-negotiable: It cuts down on unnecessary data transfer and speeds up the query.
• Double-check field mappings: Using a text field directly in a terms aggregation will return tokenized fragments, not full unique values. Always use the keyword variant.
• Wildcard performance: Queries with leading * (like *value*) can be slow on large indices. If you regularly need substring searches, consider adding an n-gram analyzer to fieldB for faster matching.

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