Hi i am trying to do group by nested and non nested fields.I want to do group by on 1 non nested fields(from_district) ,1 nested field(truck_number) and max on nested field(truck_number.score).
Requirement -: to get max score of each truck in all districts if truck is present in that district for a given sp_id eg-:
District1 ,truck1, 0.9, District2 ,truck1, 0.8, District1 ,truck2, 1.8, District2 ,truck3, 0.7, District3 ,truck4, 1.7
Below is my mapping-:
`
{
"sp_ranked_indent" : {
"mappings" : {
"properties" : {
"from_district" : {
"type" : "keyword"
},
"sp_id" : {
"type" : "long"
},
"to_district" : {
"type" : "keyword"
},
"truck_ranking_document" : {
"type" : "nested",
"properties" : {
"score" : {
"type" : "float"
},
"truck_number" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}
}
}
`
Below is the query that i tried but it is not grouping by nested and non nested field and also the max truck score is incorrect
{ "size": 0, "query": { "terms": { "sp_id": [650128], "boost": 1.0 } }, "aggregations": { "NESTED_AGG": { "nested": { "path": "truck_ranking_document" }, "aggregations": { "max_score": { "max": { "field": "truck_ranking_document.score" } }, "truck_numer": { "terms": { "field": "truck_ranking_document.truck_number.keyword", "size": 10, "min_doc_count": 1, "shard_min_doc_count": 0, "show_term_doc_count_error": false, "order": [{ "_count": "desc" }, { "_key": "asc" }] } }, "fromdistrictagg": { "reverse_nested": {}, "aggregations": { "fromDistrict": { "terms": { "field": "from_district", "size": 10, "min_doc_count": 1, "shard_min_doc_count": 0, "show_term_doc_count_error": false, "order": [{ "_count": "desc" }, { "_key": "asc" }] } } } } } } } }