I've been trying to find the right way to retrieve associated information from higher levels of nested documents. (I did search but I didn't see a previous topic that exactly matches my question. ) Here is some completely made up data that illustrates the problem:
{
"_index" : "foo",
"_type" : "gazette",
"_id" : "1",
"_version" : 1,
"found" : true,
"_source":{
"Country" : {
countryName" : "France",
"Province": [{
"provinceId" : 32,
"City":[
{"label":"Paris", "count":118},
{"label" : "Reims", "count":14}
]
},{
"provinceId" : 24,
"City" : [
{"label":"Lyons", "count":5},
{"label" : "Toulouse", "count":15}
]
}
]
}
}
}
There are two layers of nesting here, at the "Province" level and at the "City" layer. I want to be able to be able to retrieve the city labels for cities with a count above a threshold, but also, in each case, retrieve the associated provinceId (which is in an "uncle" or "aunt" relationship with the city label).
Here is the mapping:
{
"foo" : {
"mappings" : {
"gazette" : {
"properties" : {
"Country" : {
"properties" : {
"Province" : {
"type" : "nested",
"properties" : {
"City" : {
"type" : "nested",
"properties" : {
"count" : {
"type" : "long"
},
"label" : {
"type" : "string"
}
}
},
"provinceId" : {
"type" : "long"
}
}
},
"countryName" : {
"type" : "string"
}
}
}
}
}
}
}
}
Here is a sample query that retrieves inner hits only from the lower level:
curl -XPOST "http://192.168.1.202:9200/foo/gazette/_search?pretty" -d '{
"query" : {
"bool" : {
"must" : [ {
"nested" : {
"query" : {
"range" : {
"Country.Province.City.count" : {
"from" : 60,
"to" : null,
"include_lower" : true,
"include_upper" : true
}
}
},
"path" : "Country.Province.City",
"inner_hits" : {
"size" : 1000,
"_source" : true
}
}
}
]
}
},
"_source" : false
}
'
The above returns all labels and counts for cities exceeding a count of 60, but the inner hit structure returned does not give any info about the provinceId for the returned cities. On the other hand, I can try a doubly nested query with two layers of inner hits such as the follows:
curl -XPOST "http://192.168.1.202:9200/foo/gazette/_search?pretty" -d '{
"query" : {
"bool" : {
"must" : [ {
"nested" : {
"path" : "Country.Province",
"inner_hits" : { "_source" : true, "size" : 1000 },
"query" : {
"nested" : {
"path" : "Country.Province.City",
"inner_hits" : {
"size" : 1000,
"_source" : true
},
"query" : {
"range" : {
"Country.Province.City.count" : {
"from" : "60",
"to" : null,
"include_lower" : true,
"include_upper" : true
}
}
}
}
}
}
}
]
}
},
"_source" : false
}
'
Although the higher level nest here does include the provinceId for the cities I want, the returned higher level inner hits structure for each province includes cities with counts below the threshold. In my current real-life example, I am forced to paw through the returned higher level inner hits in the Java code and manually exclude the cities with counts below threshold. This just seems the wrong thing to have to do.
So is there a way in the above query to return both the city labels and the associated provinceIds for only the cities with count above threshold. If at all possible I would like to avoid reindexing. There is a lot of data.
Thanks for any help you can provide!