Hello experts and fellow elastic enthusiasts,
Am a beginner to elastic search and working on writing a search query which filters data from one index by using data from another index (am using terms lookup to achieve it). My problem is on the index which acts as a filter there are 2 fields (name and details), name being a text field and details an array.
on my query i need to filter data from an index where one field on source document matches name and another field (an array) from the same document should match one of the values in the details field of the filter index.
source index (on which the filter needs to be applied)
document1
GET source_index/_doc/1
{
"Field1": "Data10",
"Field2": "Data20",
"Field3" : ["ArrayData1", "ArrayData2", "ArrayData3"]
....
}
GET source_index/_doc/2
{
"Field1": "Data10",
"Field2": "Data20",
"Field3" : ["ArrayData11", "ArrayData12", "ArrayData13"]
....
}
GET source_index/_doc/3
{
"Field1": "Data11",
"Field2": "Data21",
"Field3" : ["ArrayData11", "ArrayData12", "ArrayData13"]
....
}
filter index (which is used to filter data from above index)
GETfilterindex/_doc/test1
{
"details": [{"Field1": "Data10", "Field3" : ["ArrayData1"]}
....
]
}
GETfilterindex/_doc/test2
{
"details": [
{"Field1": "Data11", "Field3" : ["ArrayData1", "ArrayData2"]}
....
]
}
GETfilterindex/_doc/test3
{
"details": [
{"Field1": "Data10", "Field3": ["ArrayData13", "ArrayData7"] },
{"Field1": "Data11", "Field3": ["ArrayData5", "ArrayData9"]}
....
]
}
we will take one document from filter_index to extract the documents from source_index, only those documents which match all the fields should be pulled out.
so when i use document "test1" from filter_index as my search condition in terms lookup, it should fetch only document with id "1" from source
similarly, if i use "test3" document, it should only fetch document with id "2" as the result but it gives me both document 2 and 3 as output.
Request you to help me solve this use case.
My search query :
GET source_index/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"Field1": {
"index": "filter_index",
"type": "_doc",
"id": "test1",
"path": "details.Field1"
}
}
},
{
"terms": {
"Field3": {
"index": "filter_index",
"type": "_doc",
"id": "test1",
"path": "details.Field3"
}
}
}
]
}
}
}