# AWS Elasticsearch 7.7 : Query index for one field to match a subset of chars in another text field in a document? Can it be done?

**URL:** <https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509>\
**Category:** Elasticsearch\
**Created:** [November 24, 2020, 12:49pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509 "2020-11-24T12:49:04Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![yoyomonkey](https://avatars.discourse-cdn.com/v4/letter/y/8baadc/32.png) [@yoyomonkey](https://discuss.elastic.co/u/yoyomonkey)\
**Post date:** [November 24, 2020, 12:49pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/1 "2020-11-24T12:49:04Z")

</div>

Hi everyone,

I hope someone can guide me with some guides or links on this please .

I have an index with two fields of interest : adapterid and country and I am trying to write a query without going thorugh every permutation.

adapterid : is a text field and the string consists of a countrycode followed by a string of text ie "ukoffnetwork" or "ukonnetwork" or "aeoffnetwork" .. there is one for every single country which is about 100+  
and in the same document there is a  
country field : ie "uk" , "ae" , "de" "us" etc

Now I want to create a search that will return every document that adapterid does not match the country code .

```auto
    GET /testindex-f-audit/_search
    {
    "query": {
        "bool": {
          "must": [],
          "filter": [
            {
              "match_all": {}
            },
            {
              "match_phrase": {
                "adapterid": "AEOffNetwork"
              }
            }
          ],
          "should": [],
          "must_not": [
            {
              "match_phrase": {
                "country": "AE"
              }
            }
          ]
        }
      }
    }

```

but i dont want to write a query for every single country

Is there a way to write a query where i can return all documents where the first 2 characters (subset) of one field does not match the contents of another field in the same document?

I am using aws elasticsearch so i am on 7.7 and i have looked at the wildcard keyword but i think that is a 7.10 but hasn't anyone anything similar to this in the past .

Any help would be grately appreciated.

Cheers

yoyomonkey

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 24, 2020, 1:08pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/2 "2020-11-24T13:08:11Z")

</div>

Use a query based on a scripted field that compares the two fields you described.

---

<div class="post-metadata">

**Author:** ![yoyomonkey](https://avatars.discourse-cdn.com/v4/letter/y/8baadc/32.png) [@yoyomonkey](https://discuss.elastic.co/u/yoyomonkey)\
**Post date:** [November 24, 2020, 1:56pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/3 "2020-11-24T13:56:42Z")

</div>

Thank you Christian for the . I am testing out the query i have so far before i create a scripted field containing the value but how do i compare the first 2 characters of the adapater id with the country ?

Am i going in the right direction for creating the right field? (below)

```auto
{
  "bool": {
    "filter": {
      "script": {
        "script": "doc['adapterid'].value != doc['country'].value"
    }
  }
}

```

Many thanks again.

Cheers

yoyomonkey

---

<div class="post-metadata">

**Author:** ![yoyomonkey](https://avatars.discourse-cdn.com/v4/letter/y/8baadc/32.png) [@yoyomonkey](https://discuss.elastic.co/u/yoyomonkey)\
**Post date:** [November 25, 2020, 11:13am UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/4 "2020-11-25T11:13:09Z")

</div>

Hi Christian,

Thank you for the advice. I have tried the following but it seems the search is just returning everything . I have tried == and != to see if the results are different .

I need to create a field that will either return true or false is the first 2 letters of adapterid matches the country field.

so in the case below IT of ITOffNetwork matches the country field IT.

A typical document is

```auto
     {
        "_index" : "storm-test-pk-audit",
        "_type" : "_doc",
        "_id" : "49612776696518265133771728348729303449084247219025657986.0",
        "_score" : 1.0,
        "_source" : {
          "timestamp" : "2020-11-19 14:05:14,876",
          "trackingid" : "tid:Pitmro_EwrF0",
          "event" : "AUTHN_ATTEMPT",
          "subject" : "",
          "app" : "",
          "connectionid" : "https://test.mintos.mooncow.com",
          "protocol" : "SAML20",
          "host" : "storm-test-lubo-sso-1b",
          "role" : "IdP",
          "status" : "inprogress",
          "adapterid" : "ITOffNetwork",
          "description" : "",
          "responsetime" : "34",
          "useragent" : "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET CLR 3.5.30729)",
          "city" : "Milan",
          "country" : "IT",
          "browser" : "IE",
          "os" : "Windows",
          "location" : {
            "lat" : 45.443,
            "lon" : 9.195
          }
        }
      }
    ]
  }
}

```

I am worried that if i create a scripted field it could break my kibana dashboards and index so I just wanted to see if i could first of all return documents that either matched or did not match but both just returns everything.

```auto
GET storm-test-pk-audit/_search

{
  "query":{
    "bool":{
      "must":{
        "script": {
          "lang": "painless",
          "inline": "def result; result = doc['adapterid'].value.substring(2) != doc['country'].value; return result;"
        }
      }
    }
  }
}

```

Can anyone point me to any links or could tell me where i am going wrong? This is the first query like this i am writing so any help would be really appreciated.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 25, 2020, 1:49pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/5 "2020-11-25T13:49:05Z")

</div>

> [@yoyomonkey](#):
>
> I am using aws elasticsearch so i am on 7.7 and i have looked at the wildcard keyword but i think that is a 7.10 but hasn't anyone anything similar to this in the past .

Not directly related to your question but did you look at [Cloud by Elastic](https://www.elastic.co/cloud), also available if needed from [AWS Marketplace](https://aws.amazon.com/marketplace/pp/B01N6YCISK) ?

Cloud by elastic is one way to have access to **all features** , all managed by us. Think about what is there yet like Security, Monitoring, Reporting, SQL, Canvas, Maps UI, Alerting and built-in solutions named [Observability](https://www.elastic.co/observability), [Security](https://www.elastic.co/security), [Enterprise Search](https://www.elastic.co/enterprise-search) and what is coming next 🙂 ...

And the service is always up to date with the latest Elastic versions.

---

<div class="post-metadata">

**Author:** ![yoyomonkey](https://avatars.discourse-cdn.com/v4/letter/y/8baadc/32.png) [@yoyomonkey](https://discuss.elastic.co/u/yoyomonkey)\
**Post date:** [November 25, 2020, 5:30pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/6 "2020-11-25T17:30:09Z")

</div>

Thanks David. I have had a look at Elastic by cloud and to be honest as I am still learning and developing this technology internally I am happy with AWS elasticsearch currently.

I have learned so much from a few months back and thought i would come on to the forums for advise and guidance.

Cheers

yoyomonkey

---

<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:** [December 23, 2020, 5:30pm UTC](https://discuss.elastic.co/t/aws-elasticsearch-7-7-query-index-for-one-field-to-match-a-subset-of-chars-in-another-text-field-in-a-document-can-it-be-done/256509/7 "2020-12-23T17:30:15Z")

</div>

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