Btukfyl

I have an instance of Elasticsearch running with thousands of documents. My index has 2 fields like this:

|____Type_____|__ Date_added __ |

| walking | 2018-11-27T00:00:00.000 |

| walking | 2018-11-26T00:00:00.000 |

| running | 2018-11-24T00:00:00.000 |

| running | 2018-11-25T00:00:00.000 |

| walking | 2018-11-27T04:00:00.000 |

I want to group by and count how many matches were found for the "type" field, in a certain range.
In SQL I would do something like this:

 select type,  

        count(type)

 from index

 where date_added between '2018-11-20' and '2018-11-30'

 group by type

I want to get something like this:

| type | count |

| running | 2 |

| walking | 3 |

I'm using the High Level Rest Client api in my project, so far my query looks like this, it's only filtering by the start and end time:

SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();

            sourceBuilder.query(QueryBuilders

                    .boolQuery()

                        .must(QueryBuilders

                            .rangeQuery("date_added")

                                .from(start.getTime())

                                .to(end.getTime()))

                        )

                    );

How can I do a "group by" in the "type" field? Is it possible to do this in ElasticSearch?

That's a good start! Now you need to add a terms aggregation to your query:

SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();

sourceBuilder.query(QueryBuilders.boolQuery()

                    .must(QueryBuilders

                        .rangeQuery("date_added")

                            .from(start.getTime())

                            .to(end.getTime()))

                    )

                   );



// add these two lines

TermsAggregationBuilder groupBy = AggregationBuilders.terms("byType").field("type.keyword");

sourceBuilder.aggregation(groupBy);

After using Val's reply to aggregate the fields, I wanted to print the aggregations of my query together with the value of them. Here's what I did:

Terms  terms = searchResponse.getAggregations().get("byType");

Collection<Terms.Bucket> buckets = (Collection<Bucket>) terms.getBuckets();

    for (Bucket bucket : buckets) {

        System.out.println("Type: " + bucket.getKeyAsString() + " = Count("+bucket.getDocCount()+")");

    }

This is the output after running the query in an index with 2700 documents with a field called "type" and 2 different types:

Type: walking = Count(900)

Type: running = Count(1800)