Getting multiple field group by result in elasticserach using java api

Question

I have a json

{"id": 2,"name": "Chethan","address":"Banglore"}

Trying to groupby two fields id and name,

List<String> statFields = new ArrayList();
        statFields.add("name");
        statFields.add("id");

        // 2. bootstrap the query
        SearchRequestBuilder search = client.prepareSearch("student")
            .setSize(0).setFrom(0)
            .setQuery(QueryBuilders.matchAllQuery());

        // 3. add a stats aggregation for each of your fields
        for (String field : statFields) {
            search.addAggregation(AggregationBuilders.terms(field+"_stats").field(field));
        }

        // 4. execute the query
        SearchResponse response = search.execute().actionGet();

        for(String field : statFields) {
        Terms termAgg = (Terms) response.getAggregations().get(field+"_stats");

        for (Terms.Bucket entry : termAgg.getBuckets()) {
            System.out.println(entry.getKey() + " **** " + entry.getDocCount()); // Doc count
        }
        }

Below is the response

chethan**** 2
Raj**** 1
Mohan**** 1
1 **** 1
2 **** 1
3 **** 1

But I need combined response like sql,

name    id     count
chethan 1         1

is it possible through elasticsearch java api

Answer 1

You should have used subAggregation plus use keyword type for aggregations.

Java Rest High-Level Client

Assuming your mappings look like:

PUT student
{
  "mappings": {
    "doc": {
      "properties": {
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "id": {
          "type": "keyword"
        },
        "address": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

In order to group by name and id you should use this query (low level query):

GET student/_search
{
  "size": 0,
  "aggs": {
    "name": {
      "terms": {
        "field": "name.keyword",
        "size": 10
      },"aggs": {
        "id": {
          "terms": {
            "field": "id",
            "size": 10
          }
        }
      }
    }
  }
}

In java the query above is similar to:

SearchResponse response = client.search(new SearchRequest("student")
    .source(new SearchSourceBuilder()
            .size(0)
            .aggregation(
                    AggregationBuilders.terms("by_name").field("name.keyword")
                            .subAggregation(AggregationBuilders.terms("by_id")
                                    .field("id")
                            )
            );

If you want to use your code, probably something like that :

// 2. bootstrap the query
SearchRequestBuilder search = client.prepareSearch("student")
    .setSize(0).setFrom(0)
    .setQuery(QueryBuilders.matchAllQuery());

// 3. add a stats aggregation for each of your fields

TermsAggregationBuilder aggregation = AggregationBuilders.terms("name_stats").field("name.keyword");
aggregation.subAggregation(AggregationBuilders.terms("id_stats").field("id"));  
search.aggregation(aggregation);


// 4. execute the query
SearchResponse response = search.execute().actionGet();


    Terms termAgg = (Terms)response.getAggregations().get("name_stats");

    for (Terms.Bucket entry: termAgg.getBuckets()) {
        if (entry.getDocCount() != 0) {
            Terms terms =entry.getAggregations().get("id");
            Collection<Terms.Bucket> buckets = terms.getBuckets();
            for (Bucket sub : buckets ) {
                System.out.println((int) sub.getDocCount());
                System.out.println(sub.getKeyAsString());
            }
        }
    }

I removed the for loop. you should design your own structure now that you have to use sub-aggregations.

UPDATE

Is this what you want?

GET student/_search
{
  "size": 0, 
    "aggs" : {
        "name_id" : {
            "terms" : {
                "script" : {
                    "source": "doc['name.keyword'].value + '_' + doc['id'].value",
                    "lang": "painless"
                }
            }
        }
    }
}

I hope this is what you aimed for.