Could not able to Perform aggregations along with filters

Elastic Stack Elasticsearch
1

Hi,

We are using elasticsearch to store our data and we are performing calculations on stored data. We are applying filters as well as aggregations on the stored data. But we are not getting results as expected. Need help on applying filters and aggregations on the stored data. Our aim is to apply filters as well aggregations in a single query.

This particular query is not working as expected.

{
"aggs": {
"filtered": {
"filter": {
"bool": {
"must": [
{
"term": {
"Name": "Stokes"
}
},
{
"term": {
"Name": "Roshan"
}
},
{
"range": {
"marks": {
"gte": 55
}
}
}
]
}
},
"aggs": {
"avg_grade_perYear": {
"terms": {
"field": "year"
},
"aggs": {
"sum_marks": {
"sum": {
"field": "marks"
}
}
}
}
}
}
}
}

Regards,
Ravi Devineni

2

what's not expected? is it not returning results? incorrect results? more detail would be nice.

3

This is the Sample data I have in elasticsearch
{
"took": 1,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 5,
"max_score": 1,
"hits": [
{
"_index": "bighalf",
"_type": "excel",
"_id": "AVE0rgXqe0-x669Gsae3",
"_score": 1,
"_source": {
"Name": "Taylor",
"grade": 9,
"year": 2016,
"marks": 54,
"subject": "Mathematics",
"Gender": "male",
"dob": "13/09/2000"
}
},
{
"_index": "bighalf",
"_type": "excel",
"_id": "AVE0rvTHe0-x669Gsae5",
"_score": 1,
"_source": {
"Name": "Marsh",
"grade": 9,
"year": 2015,
"marks": 70,
"subject": "Mathematics",
"Gender": "male",
"dob": "22/11/2000"
}
},
{
"_index": "bighalf",
"_type": "excel",
"_id": "AVE0sBbZe0-x669Gsae7",
"_score": 1,
"_source": {
"Name": "Taylor",
"grade": 3,
"year": 2015,
"marks": 87,
"subject": "physics",
"Gender": "male",
"dob": "13/09/2000"
}
},
{
"_index": "bighalf",
"_type": "excel",
"_id": "AVE0rWz4e0-x669Gsae2",
"_score": 1,
"_source": {
"Name": "Stokes",
"grade": 9,
"year": 2015,
"marks": 91,
"subject": "Mathematics",
"Gender": "male",
"dob": "21/12/2000"
}
},
{
"_index": "bighalf",
"_type": "excel",
"_id": "AVE0roT4e0-x669Gsae4",
"_score": 1,
"_source": {
"Name": "Roshan",
"grade": 9,
"year": 2015,
"marks": 85,
"subject": "Mathematics",
"Gender": "male",
"dob": "12/12/2000"
}
}
]
}
}

Am using Transport client to query data.
example code:
String[] names = {"Stokes","Roshan"};
BoolQueryBuilder builder = QueryBuilders.boolQuery();
AggregationBuilder<?> aggregation = AggregationBuilders.filters("agg")
.filter(builder.filter(QueryBuilders.termsQuery("Name", "Taylor"))
.filter(QueryBuilders.rangeQuery("grade").lt(9.0)))
.subAggregation(AggregationBuilders.terms("by_year").field("year")
.subAggregation(AggregationBuilders.sum("sum_marks").field("marks"))
.subAggregation(AggregationBuilders.sum("sum_grade").field("grade")));
SearchResponse response = client.prepareSearch(index).setTypes(datasquareID).addAggregation(aggregation)
.execute().actionGet();
System.out.println(response.toString());

I wanted to calculate sum of marks and sum of grades with names "Stokes" or "Roshan" who's grade is less than 9 and group them by "year". Please let me know whether my approach is correct or not. Please let me know your suggestions as well.

Response Am getting after running above code.

{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 5,
"max_score" : 1.0,
"hits" : [ {
"_index" : "bighalf",
"_type" : "excel",
"_id" : "AVE0rgXqe0-x669Gsae3",
"_score" : 1.0,
"_source":{"Name":"Taylor","grade":9,"year":2016,"marks":54,"subject":"Mathematics","Gender":"male","dob":"13/09/2000"}
}, {
"_index" : "bighalf",
"_type" : "excel",
"_id" : "AVE0rvTHe0-x669Gsae5",
"_score" : 1.0,
"_source":{
"Name": "Marsh",
"grade": 9,
"year": 2015,
"marks": 70,
"subject": "Mathematics",
"Gender": "male",
"dob": "22/11/2000"
}

}, {
  "_index" : "bighalf",
  "_type" : "excel",
  "_id" : "AVE0sBbZe0-x669Gsae7",
  "_score" : 1.0,
  "_source":{"Name":"Taylor","grade":3,"year":2015,"marks":87,"subject":"physics","Gender":"male","dob":"13/09/2000"}
}, {
  "_index" : "bighalf",
  "_type" : "excel",
  "_id" : "AVE0rWz4e0-x669Gsae2",
  "_score" : 1.0,
  "_source":{
"Name": "Stokes",
"grade": 9,
"year": 2015,
"marks": 91,
"subject": "Mathematics",
"Gender": "male",
"dob": "21/12/2000"

}

}, {
  "_index" : "bighalf",
  "_type" : "excel",
  "_id" : "AVE0roT4e0-x669Gsae4",
  "_score" : 1.0,
  "_source":{
"Name": "Roshan",
"grade": 9,
"year": 2015,
"marks": 85,
"subject": "Mathematics",
"Gender": "male",
"dob": "12/12/2000"

}

} ]

},
"aggregations" : {
"agg" : {
"buckets" : [ {
"doc_count" : 0,
"by_year" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
}
} ]
}
}
}