Hello,
I'm trying to write a query to form an aggregation of all the colleges under a university, put them into country id buckets and get the top colleges based out of each country.
Somehow I'm not getting what I'm expecting as its not filtering based on "university.id" and not creating the buckets on country.fid
The same query works for ES v5.3.2 but fails to filter based on "university.id" in v7.10.0.
The university and country are two different nested objects under the college document.
Any help would be appreciated!
Following it the query I'm using.
**Mapping**
PUT /college/_mapping/
{
"properties": {
"id": { "type": "keyword", "index": true},
"collegeRank": { "type": "integer", "index": true},
"university": {
"type": "nested",
"properties": {
"id": { "type": "keyword", "index": true}
}
},
"country": {
"type": "nested",
"properties": {
"fid": {"type": "keyword", "index": true}
}
}
}
}
**Sample data**
{
"_index" : "college",
"_type" : "_doc",
"_id" : "1",
"_score" : null,
"_source" : {
"id" : "1",
"collegeRank" : 18,
"university" : {
"id" : "100"
},
"country" : {
"fid" : "USA"
}
},
"sort" : [
"1"
]
},
{
"_index" : "college",
"_type" : "_doc",
"_id" : "2",
"_score" : null,
"_source" : {
"id" : "2",
"collegeRank" : 120,
"university" : {
"id" : "100"
},
"country" : {
"fid" : "USA"
}
},
"sort" : [
"2"
]
},
{
"_index" : "college",
"_type" : "_doc",
"_id" : "3",
"_score" : null,
"_source" : {
"id" : "3",
"collegeRank" : 300,
"university" : {
"id" : "200"
},
"country" : {
"fid" : "GBR"
}
},
"sort" : [
"3"
]
},
{
"_index" : "college",
"_type" : "_doc",
"_id" : "4",
"_score" : null,
"_source" : {
"id" : "4",
"collegeRank" : 150,
"university" : {
"id" : "200"
},
"country" : {
"fid" : "DEU"
}
},
"sort" : [
"4"
]
},
{
"_index" : "college",
"_type" : "_doc",
"_id" : "5",
"_score" : null,
"_source" : {
"id" : "5",
"collegeRank" : 2,
"university" : {
"id" : "200"
},
"country" : {
"fid" : "DEU"
}
},
"sort" : [
"5"
]
}
**Query**
GET college/_search
{
"size": 0,
"aggs" : {
"byUniversity" : {
"nested" : {
"path" : "university"
},
"aggs" : {
"byUniversityId" : {
"filter": {
"term": {
"university.id": "200"
}
},
"aggs": {
"byUniversityOrCollegeCountry" : {
"nested" : {
"path" : "country"
},
"aggs" : {
"byUniversityOrCollegeCountryId" : {
"terms" : {
"field" : "country.fid",
"size" : 500
},
"aggs" : {
"byCollege": {
"reverse_nested": {},
"aggs": {
"topCollegeHit": {
"top_hits": {
"sort": [
{"collegeRank": {"order": "asc"}}
],
"size": 1
}
}
}
}
}
}
}
}
}
}
}
}
}
}
**Output on v5.3.2**
{
"took": 3,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 5,
"max_score": 0,
"hits": []
},
"aggregations": {
"byUniversity": {
"doc_count": 5,
"byUniversityId": {
"doc_count": 3,
"byUniversityOrCollegeCountry": {
"doc_count": 3,
"byUniversityOrCollegeCountryId": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "DEU",
"doc_count": 2,
"byCollege": {
"doc_count": 2,
"topCollegeHit": {
"hits": {
"total": 2,
"max_score": null,
"hits": [
{
"_index": "college",
"_type": "col",
"_id": "5",
"_score": null,
"_source": {
"id": "5",
"collegeRank": 2,
"university": {
"id": "200"
},
"country": {
"fid": "DEU"
}
},
"sort": [
2
]
}
]
}
}
}
},
{
"key": "GBR",
"doc_count": 1,
"byCollege": {
"doc_count": 1,
"topCollegeHit": {
"hits": {
"total": 1,
"max_score": null,
"hits": [
{
"_index": "college",
"_type": "col",
"_id": "3",
"_score": null,
"_source": {
"id": "3",
"collegeRank": 300,
"university": {
"id": "200"
},
"country": {
"fid": "GBR"
}
},
"sort": [
300
]
}
]
}
}
}
}
]
}
}
}
}
}
}
**Output on v7.10.0**
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 5,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"byUniversity" : {
"doc_count" : 5,
"byUniversityId" : {
"doc_count" : 3,
"byUniversityOrCollegeCountry" : {
"doc_count" : 1,
"byUniversityOrCollegeCountryId" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "GBR",
"doc_count" : 1,
"byCollege" : {
"doc_count" : 1,
"topCollegeHit" : {
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "college",
"_type" : "_doc",
"_id" : "3",
"_score" : null,
"_source" : {
"id" : "2",
"collegeRank" : 300,
"university" : {
"id" : "200"
},
"country" : {
"fid" : "GBR"
}
},
"sort" : [
300
]
}
]
}
}
}
}
]
}
}
}
}
}
}