I have to generate below query using rest high level client and i have tried the following query but it is not able to form the query as below wanted to know where it is going wrong in my current aggregation code top hits should appear before the sum aggregations and also i am using rest high level client to connect to the elastic search.
Required Query:
{
"aggregations": {
"by_destination": {
"terms": {
"field": "destinationLocation.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_trans": {
"terms": {
"field": "transportMode.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_sourcelocation": {
"terms": {
"field": "sourceLocation.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_shipdate": {
"terms": {
"field": "shipDate",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_arrival": {
"terms": {
"field": "arrivalDate",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"docs": {
"top_hits": {
"size": 10,
"_source": [
"id"
]
}
},
"quantity": {
"sum": {
"field": "transferQuantity"
}
},
"transfercost": {
"sum": {
"field": "transferCost"
}
},
"revenueRecovered": {
"sum": {
"field": "revenueRecovered"
}
}
}
}
}
}
}
}
}
}
}
}
}
}
The Code:
TopHitsAggregationBuilder topHitsAggregationBuilder = AggregationBuilders.topHits("top").fetchSource("id", "").size(1);
AggregationBuilder itemAgg = AggregationBuilders.terms("by_item").field("item.keyword");
AggregationBuilder destinationAgg = AggregationBuilders.terms("by_destination").field("destinationLocation.keyword");
AggregationBuilder transportAgg = AggregationBuilders.terms("by_trans").field("transportMode.keyword");
AggregationBuilder sourceAgg = AggregationBuilders.terms("by_sourcelocation").field("sourceLocation.keyword");
AggregationBuilder shipDateAgg = AggregationBuilders.terms("by_shipdate").field("shipDate");
AggregationBuilder arrivalDateAgg = AggregationBuilders.terms("by_arrival").field("arrivalDate");
AggregationBuilder quantityAgg = AggregationBuilders.sum("quantity").field("transferQuantity");
AggregationBuilder transferCostAgg = AggregationBuilders.sum("transfercost").field("transferCost");
AggregationBuilder revenueRecoveredAgg = AggregationBuilders.sum("revenueRecovered").field("revenueRecovered");
AggregationBuilder aggregation = itemAgg.subAggregation(destinationAgg
.subAggregation(transportAgg.subAggregation(sourceAgg.subAggregation(shipDateAgg.subAggregation(arrivalDateAgg)
.subAggregation(topHitsAggregationBuilder).subAggregation(quantityAgg)
.subAggregation(transferCostAgg).subAggregation(revenueRecoveredAgg)))));
//searchSourceBuilder.size(0);
searchSourceBuilder.aggregation(aggregation);
searchRequest.source(searchSourceBuilder);
actual generating is:
{
"aggregations": {
"by_item": {
"terms": {
"field": "item.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_destination": {
"terms": {
"field": "destinationLocation.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_trans": {
"terms": {
"field": "transportMode.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_sourcelocation": {
"terms": {
"field": "sourceLocation.keyword",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_shipdate": {
"terms": {
"field": "shipDate",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
},
"aggregations": {
"by_arrival": {
"terms": {
"field": "arrivalDate",
"size": 10,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [
{
"_count": "desc"
},
{
"_key": "asc"
}
]
}
},
"top": {
"top_hits": {
"from": 0,
"size": 1,
"version": false,
"explain": false,
"_source": {
"includes": [
"id"
],
"excludes": [
""
]
}
}
},
"quantity": {
"sum": {
"field": "transferQuantity"
}
},
"transfercost": {
"sum": {
"field": "transferCost"
}
},
"revenueRecovered": {
"sum": {
"field": "revenueRecovered"
}
}
}
}
}
}
}
}
}
}
}
}
}
}
Top_hits aggregation doesn't accept sub aggregations. So solution was to change below query
.subAggregation(topHitsAggregationBuilder.subAggregation(quantityAgg)
to
.subAggregation(topHitsAggregationBuilder).subAggregation(quantityAgg)