How to use elasticsearch java api to apply aggregations on a query result

Elastic Stack Elasticsearch
1

I am using co.elastic.clients:elasticsearch-java:9.0.1

I am trying to apply a query and then apply aggregations
the query is about to get the user name and visiting dates within a date range

the result could be:
userA 2025-06-01
userA 2025-06-01
userA 2025-06-02
userA 2025-06-02
userB 2025-06-01
userB 2025-06-02
userB 2025-06-02
userB 2025-06-03

the aggregations is about to get the number of visiting days for each user
the result in this case is:
userA: 2
userB: 3

I tried apply a query then apply termAggregations, also tried apply a filterAggregation then apply termAggregations, both not work

not sure how to achieve this?

select username, count(visitdate)
select distinct username, visitdate
from ...
where visitdate between ... and...
group by username

code samples are appreciated

thank you in advance

2

looks like this is caused by the special character (.) in the field name.
the field name is xxx.xx

3

Hello!

If you're not sure which aggregation to use, it's best to experiment first with Kibana, since it's faster to copy paste examples from the documentation, and then when you have something that works, translate it to Java :slight_smile:

I think Terms Aggregation should work in this case, let's try it:

  1. Create a new index to hold the user data
PUT /users-log
{
  "mappings": {
    "properties": {
      "user": {
        "type": "keyword"
      }
    }
  }
}
  1. Bulk post data to the index
POST users-log/_bulk?refresh=true
{ "index": { "_id": "1" } }
{ "user": "userA", "date": "2025-06-01"}
{ "index": { "_id": "2" } }
{ "user": "userA", "date": "2025-06-02"}
{ "index": { "_id": "3" } }
{ "user": "userB", "date": "2025-06-02"}
  1. Terms Aggregation
GET users-log/_search
{
  "aggs": {
    "user-log": {
      "terms": { "field": "user" }
    }
  }
}

In the relevant part of the response, here is the result!

...
"aggregations": {
    "user-log": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "userA",
          "doc_count": 2
        },
        {
          "key": "userB",
          "doc_count": 1
        }
      ]
    }
  }

And lastly, here's how to translate the aggregation to Java:

esClient.search(s -> s
    .aggregations("user-log", a -> a
        .terms(t -> t
            .field("user")
        )
    )
,Object.class);
4

@ltrotta - I think @nobodyg is rather looking for this:

POST users-log/_bulk?refresh=true
{ "index": { "_id": "1" } }
{ "user": "userA", "date": "2025-06-01"}
{ "index": { "_id": "2" } }
{ "user": "userA", "date": "2025-06-01"}
{ "index": { "_id": "3" } }
{ "user": "userA", "date": "2025-06-02"}
{ "index": { "_id": "4" } }
{ "user": "userA", "date": "2025-06-02"}
{ "index": { "_id": "5" } }
{ "user": "userB", "date": "2025-06-01"}
{ "index": { "_id": "6" } }
{ "user": "userB", "date": "2025-06-02"}
{ "index": { "_id": "7" } }
{ "user": "userB", "date": "2025-06-02"}
{ "index": { "_id": "8" } }
{ "user": "userB", "date": "2025-06-03"}

and aggr as follows:

GET users-log/_search
{
  "size": 0,
  "aggs": {
    "aggr1": {
      "terms": {
        "field": "user"
      },
      "aggs": {
        "aggr2": {
          "cardinality": {
            "field": "date"
          }
        }
      }
    }
  }
}

which generates these results:

  "aggregations": {
    "aggr1": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "userA",
          "doc_count": 4,
          "aggr2": {
            "value": 2
          }
        },
        {
          "key": "userB",
          "doc_count": 4,
          "aggr2": {
            "value": 3
          }
        }
      ]
    }
  }

I'm not a java coder, so leave turning that into Java code for someone else.

5

@RainTown thanks for clarifying, I went for a more generic approach just to provide guidance, but your example is more precise :slight_smile: