当前位置：首页 > article >正文

es的date类型字段按照原生格式进行分组聚合

article 2025/1/23 12:04:56

PUT student2
{
"mappings": {
  "properties": {
    "name": {
      "type": "text",
      "analyzer": "standard"  // 使用标准分析器，适合姓名字段
    },
    "birthday": {
      "type": "date",
      "format": "yyyy||yyyy-MM||yyyy-MM-dd"  // 日期格式，可以根据需求调整
    },
    "age": {
      "type": "integer"  // 年龄字段，使用整型
    },
    "gender": {
      "type": "keyword"  // 性别字段，使用 keyword 类型来避免分词
    },
    "email": {
      "type": "keyword"  // 使用 keyword 类型来存储不需要分词的电子邮件地址
    },
    "address": {
      "type": "text"  // 地址字段，适合使用全文本分词
    },
    "registration_date": {
      "type": "date",
      "format": "yyyy-MM-dd'T'HH:mm:ss"  // 注册日期，包含时间
    },
    "is_active": {
      "type": "boolean"  // 是否激活标记，使用布尔类型
    }
  }
}
}

插入数据

POST student1/_doc/1
{
  "name": "Alice",
  "birthday": "2000",
  "age": 24,
  "gender": "female",
  "email": "alice@example.com",
  "address": "123 Maple Street, Springfield",
  "registration_date": "2022-08-01T09:30:00",
  "is_active": true
}

POST student1/_doc/2
{
  "name": "Bob",
  "birthday": "1998-11",
  "age": 26,
  "gender": "male",
  "email": "bob@example.com",
  "address": "456 Oak Avenue, Shelbyville",
  "registration_date": "2023-01-10T14:00:00",
  "is_active": true
}

POST student1/_doc/3
{
  "name": "Charlie",
  "birthday": "1995-03-25",
  "age": 29,
  "gender": "male",
  "email": "charlie@example.com",
  "address": "789 Birch Road, Capital City",
  "registration_date": "2021-07-15T11:00:00",
  "is_active": false
}

POST student1/_doc/4
{
  "name": "Diana",
  "birthday": "1999",
  "age": 25,
  "gender": "female",
  "email": "diana@example.com",
  "address": "101 Pine Lane, Townsville",
  "registration_date": "2020-12-05T08:00:00",
  "is_active": true
}

执行查询

GET student2/_search
{
  "size": 0,
  "aggs": {
    "birthday_by_original_format": {
      "terms": {
        "script": {
          "source": """
            if (params['_source']['birthday'] != null) {
              return params['_source']['birthday'].toString();
            }
            return null;
          """
        },
        "size": 10
      }
    }
  }
}

得到结果：

{
  "took": 25,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 4,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "birthday_by_original_format": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "1995",
          "doc_count": 1
        },
        {
          "key": "1998-11",
          "doc_count": 1
        },
        {
          "key": "1999-07-20",
          "doc_count": 1
        },
        {
          "key": "2000-05-15",
          "doc_count": 1
        }
      ]
    }
  }
}

查询语句写为java:

import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.client.RestClient;
import org.apache.http.HttpHost;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.script.ScriptType;
import org.elasticsearch.script.Script;

import java.io.IOException;
import java.util.Collections;
import java.util.List;

public class ElasticsearchAggregationExample {

    public static void main(String[] args) {
        // 创建 Elasticsearch 客户端
        RestHighLevelClient client = new RestHighLevelClient(
                RestClient.builder(new HttpHost("localhost", 9200, "http"))  // 替换为你的 Elasticsearch 地址
        );

        try {
            // 构建查询请求
            SearchRequest searchRequest = new SearchRequest("student2");  // 替换为你的索引名称
            SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();

            // 构建聚合
            String scriptSource = "if (params['_source']['birthday'] != null) { " +
                                 "  return params['_source']['birthday'].toString(); " +
                                 "} " +
                                 "return null;";

            TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("birthday_by_original_format")
                    .script(new Script(ScriptType.INLINE, "painless", scriptSource, Collections.emptyMap()))
                    .size(10);  // 设置返回的桶数量

            // 将聚合添加到查询中
            sourceBuilder.aggregation(aggregationBuilder);
            sourceBuilder.size(0);  // 不返回具体文档，只返回聚合结果
            searchRequest.source(sourceBuilder);

            // 执行查询
            SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);

            // 解析聚合结果
            Terms terms = searchResponse.getAggregations().get("birthday_by_original_format");
            List<? extends Terms.Bucket> buckets = terms.getBuckets();
            for (Terms.Bucket bucket : buckets) {
                String key = bucket.getKeyAsString();  // 获取聚合的 key（原始的 birthday 值）
                long docCount = bucket.getDocCount();  // 获取文档数量
                System.out.println("Key: " + key + ", Doc Count: " + docCount);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭客户端
            try {
                client.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

终极解决方案：
给mapping的所有date类型字段添加子类型

PUT /student3
{
  "mappings": {
    "properties": {
      "address": {
        "type": "text"
      },
      "age": {
        "type": "integer"
      },
      "birthday": {
        "type": "date",
        "format": "yyyy||yyyy-MM||yyyy-MM-dd",
            "fields": {                
              "raw": {                
                "type": "keyword"
              }
            }
      },
      "email": {
        "type": "keyword"
      },
      "events": {
        "type": "nested",
        "properties": {
          "event_date": {
            "type": "date",
            "format": "yyyy||yyyy-MM||yyyy-MM-dd",
            "fields": {                
              "raw": {                
                "type": "keyword"
              }
            }
          },
          "event_type": {
            "type": "keyword"
          }
        }
      },
      "gender": {
        "type": "keyword"
      },
      "is_active": {
        "type": "boolean"
      },
      "name": {
        "type": "text",
        "analyzer": "standard"
      },
      "registration_date": {
        "type": "date",
        "format": "yyyy-MM-dd'T'HH:mm:ss",
            "fields": {                
              "raw": {                
                "type": "keyword"
              }
            }
      }
    }
  }
}

插入数据：

POST student1/_doc/1
{
  "name": "Alice",
  "birthday": "2000",
  "age": 24,
  "gender": "female",
  "email": "alice@example.com",
  "address": "123 Maple Street, Springfield",
  "registration_date": "2022-08-01T09:30:00",
  "is_active": true,
  "events": [
    {
      "event_date": "2022",
      "event_type": "graduation"
    },
    {
      "event_date": "2023",
      "event_type": "birthday"
    }
  ]
}

POST student1/_doc/2
{
  "name": "Bob",
  "birthday": "1998-11",
  "age": 26,
  "gender": "male",
  "email": "bob@example.com",
  "address": "456 Oak Avenue, Shelbyville",
  "registration_date": "2023-01-10T14:00:00",
  "is_active": true,
  "events": [
    {
      "event_date": "2022-06",
      "event_type": "meeting"
    }
  ]
}

POST student1/_doc/3
{
  "name": "Charlie",
  "birthday": "1995-03-25",
  "age": 29,
  "gender": "male",
  "email": "charlie@example.com",
  "address": "789 Birch Road, Capital City",
  "registration_date": "2021-07-15T11:00:00",
  "is_active": false,
  "events": [
    {
      "event_date": "2021-10-10",
      "event_type": "conference"
    },
    {
      "event_date": "2022-02-20",
      "event_type": "workshop"
    },
    {
      "event_date": "2023-04-05",
      "event_type": "meeting"
    }
  ]
}


POST student1/_doc/4
{
  "name": "Diana",
  "birthday": "1999-07-20",
  "age": 25,
  "gender": "female",
  "email": "diana@example.com",
  "address": "101 Pine Lane, Townsville",
  "registration_date": "2020-12-05T08:00:00",
  "is_active": true,
  "events": [
    {
      "event_date": "2022-11-10",
      "event_type": "webinar"
    }
  ]
}

根据嵌套子字段的date类型字段聚合

GET /student3_new/_search
{
  "size": 0,
  "aggs": {
    "nested_events": {
      "nested": {
        "path": "events"
      },
      "aggs": {
        "event_date_groups": {
          "terms": {
            "field": "events.event_date.raw", 
            "size": 10
          }
        }
      }
    }
  }
}

聚合结果：

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 4,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "nested_events": {
      "doc_count": 7,
      "event_date_groups": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "2021-10-10",
            "doc_count": 1
          },
          {
            "key": "2022",
            "doc_count": 1
          },
          {
            "key": "2022-02-20",
            "doc_count": 1
          },
          {
            "key": "2022-06",
            "doc_count": 1
          },
          {
            "key": "2022-11-10",
            "doc_count": 1
          },
          {
            "key": "2023",
            "doc_count": 1
          },
          {
            "key": "2023-04-05",
            "doc_count": 1
          }
        ]
      }
    }
  }
}