I try to query documents that have nested objects:
{
"name":"Adam",
"cities":[
{
"name":"California",
"activities":[
{
"name":"Hicking"
},
{
"name":"Camping"
},
{
"name":"Festival"
}
]
},
{
"name":"Miami",
"activities":[
{
"name":"Festival"
},
{
"name":"Diving"
},
{
"name":"Surfing"
}
]
}
]
}
I want to retrieve all conditions based on an activity, for example, I want to retrieve all cities where Adam wants to go to festivals. The query result should be California and Miami and if we search about what is the town where Adam will do hicking, the query result should be California.
This is what it SHOULD be, but the actual result is that when I search about the cities where Adam will do hicking, the query result was California and Miami!!
This is the query I used:
GET index/_search
{
"query":
{
"bool": {"filter": [
{"term": {
"cities.activities.name.keyword": "Hicking"
}}
]}
},"_source":["cities.name"]
}
And this is the search result:
"_index" : "index",
"_type" : "prod",
"_id" : "1",
"_score" : 0.0,
"_source" : {
"cities" : [
{
"name" : "California"
},
{
"name" : "Miami"
}
]
}
How can make the query in the right way for such a data structure?
Will nested field helps?
Should I redesign the data structure?