# Elastic Search query on multiple fields in terms lookup

**URL:** https://discuss.elastic.co/t/elastic-search-query-on-multiple-fields-in-terms-lookup/245342
**Category:** Elasticsearch
**Created:** [August 18, 2020, 4:05am UTC](https://discuss.elastic.co/t/elastic-search-query-on-multiple-fields-in-terms-lookup/245342 "2020-08-18T04:05:44Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![santoshk](https://avatars.discourse-cdn.com/v4/letter/s/58956e/32.png) [@santoshk](https://discuss.elastic.co/u/santoshk)
#### Post date: [August 18, 2020, 4:05am UTC](https://discuss.elastic.co/t/elastic-search-query-on-multiple-fields-in-terms-lookup/245342/1 "2020-08-18T04:05:44Z")

</div>

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

```auto
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)

```auto
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 :

```auto
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"
            }
          }
        }
      ]
    }
  }
}

```

---

<div class="post-metadata">

### Author: ![santoshk](https://avatars.discourse-cdn.com/v4/letter/s/58956e/32.png) [@santoshk](https://discuss.elastic.co/u/santoshk)
#### Post date: [August 18, 2020, 7:07pm UTC](https://discuss.elastic.co/t/elastic-search-query-on-multiple-fields-in-terms-lookup/245342/2 "2020-08-18T19:07:52Z")

</div>

Hi experts, appreciate any pointers on the issue

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 15, 2020, 7:08pm UTC](https://discuss.elastic.co/t/elastic-search-query-on-multiple-fields-in-terms-lookup/245342/3 "2020-09-15T19:08:00Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.