Elasticsearch聚合查询问题：按字段分组筛选文本类型聚合值

Got it, let's work through this problem to replicate your SQL query in Elasticsearch. Your goal is to group by FIELD3, grab the maximum values of FIELD1 (text type) and FIELD2, then only keep groups where the max FIELD1 equals 'SOME_TEXT'. Here's how to do it step by step:

1. Fix the Text Field Aggregation Issue #

Elasticsearch's default text field type doesn't support aggregations like max because it's split into analyzed tokens. To get the max value of a text field, you have two reliable options:

• Use the keyword sub-field of FIELD1 (most default index mappings add this automatically for text fields).
• If no keyword sub-field exists, update your index mapping to add one (this is way better than enabling fielddata for text fields, which eats up heap memory).

Example mapping update for FIELD1:

PUT /your_index_name/_mapping
{
  "properties": {
    "FIELD1": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256  // Adjust based on your typical text length
        }
      }
    }
  }
}

2. Full Aggregation Query (Equivalent to Your SQL) #

This query uses terms for grouping, max aggregations for your two fields, and bucket_selector to replicate the HAVING clause:

{
  "size": 0,  // Skip raw documents—we only care about aggregation results
  "aggs": {
    "group_by_field3": {
      "terms": {
        "field": "FIELD3"  // Matches GROUP BY FIELD3
      },
      "aggs": {
        "max_field1": {
          "max": {
            "field": "FIELD1.keyword"  // Use keyword sub-field for text max
          }
        },
        "max_field2": {
          "max": {
            "field": "FIELD2"  // Numeric fields work directly with max
          }
        },
        "filter_matching_groups": {
          "bucket_selector": {
            "buckets_path": {
              "f1_value": "max_field1"
            },
            "script": "params.f1_value == 'SOME_TEXT'"  // Matches HAVING F1 = 'SOME_TEXT'
          }
        }
      }
    }
  }
}

Key Breakdown of the Query #

• size: 0: Disables returning individual documents, which speeds up the query since we only need aggregation results.
• terms aggregation: Groups all documents by unique values of FIELD3, just like GROUP BY in SQL.
• max aggregations:
  • For FIELD1, FIELD1.keyword gives us the lexicographically largest exact string (since keyword fields store unanalyzed text).
  • For FIELD2, if it's a numeric type (integer, float, etc.), the max aggregation directly grabs the highest numeric value.
• bucket_selector: This is Elasticsearch's version of HAVING. It filters out any groups where the max FIELD1 doesn't match your target text.

Quick Notes #

• If you're dealing with numeric strings (e.g., '10', '2') and need numeric max instead of lexicographic, store those values as numeric types instead of text.
• Avoid enabling fielddata on text fields unless you have no other option—it's not scalable for large datasets.

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