CODEWITHSUNDEEP
javaelasticsearchaggregation
Joshua
Joshua

Reputation: 125

Aggregation date using Elasticsearch RestHighLevelClient

I'm using Elasticsearch RestHighLevelClient and try to implement sql below:

select format(date,'yyyy-MM-dd'), count(*) from order group by format(date,'yyyy-MM-dd')

    SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
    sourceBuilder.query(QueryBuilders.matchAllQuery());
    sourceBuilder.aggregation(AggregationBuilders.terms("date_count").field("time").format("yyyy-MM-dd"));
    SearchRequest searchRequest = new SearchRequest("order_*");
    searchRequest.source(sourceBuilder);
    SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
    Aggregations aggregations = searchResponse.getAggregations();

I got the result but with many repeat date results like:

buckets:
  bucket1:
    key: 1583390009000
    keyAsString: 2020-03-05
    docCount: 4
  bucket2:  
    key: 1583391748000
    keyAsString: 2020-03-05
    docCount: 5

How should I avoid repeat date? I mean the same date(such as 2020-03-05) count as one bucket.

Here’s some sample documents

  {
    "_index": "order_202003",
    "_type": "_doc",
    "_id": "1568274527051",
    "_score": 1,
    "_source": {
      "auto": 0,
      "collection_currency": "RMB",
      "collection_value": 0,
      "customerid": "",
      "freight": 0,
      "gs": 201101,
      "status": "accept",
      "time": "2020-03-05T10:01:23.000+0800"
    }
  },
  {
    "_index": "order_202003",
    "_type": "_doc",
    "_id": "1568274527078",
    "_score": 1,
    "_source": {
      "auto": 0,
      "collection_currency": "RMB",
      "collection_value": 0,
      "customerid": "",
      "freight": 0,
      "gs": 413572,
      "status": "accept",
      "time": "2020-03-05T17:29:53.000+0800"
    }
  }

Upvotes: 0

Views: 1607

Answers (1)

Nishant
Nishant

Reputation: 7864

You should use date histogram aggregation instead:

SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
sourceBuilder.query(QueryBuilders.matchAllQuery());
//sourceBuilder.aggregation(AggregationBuilders.terms("date_count").field("time").format("yyyy-MM-dd"));
sourceBuilder.aggregation(AggregationBuilders.dateHistogram("date_count").calendarInterval(DateHistogramInterval.DAY).field("time"));
SearchRequest searchRequest = new SearchRequest("order_*");
searchRequest.source(sourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
Aggregations aggregations = searchResponse.getAggregations();

Upvotes: 1

Related Questions