# Find documents where field1 is equal to substring of field2

**URL:** <https://discuss.elastic.co/t/find-documents-where-field1-is-equal-to-substring-of-field2/108025>\
**Category:** Elasticsearch\
**Created:** [November 16, 2017, 9:25pm UTC](https://discuss.elastic.co/t/find-documents-where-field1-is-equal-to-substring-of-field2/108025 "2017-11-16T21:25:53Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pierig\_LeSaux](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@Pierig\_LeSaux](https://discuss.elastic.co/u/Pierig_LeSaux)\
**Post date:** [November 16, 2017, 9:25pm UTC](https://discuss.elastic.co/t/find-documents-where-field1-is-equal-to-substring-of-field2/108025/1 "2017-11-16T21:25:53Z")

</div>

Hello,

I am writing a query to find all documents that have a field equal to a substring of another field

```auto
{
    "query": {
        "bool" : {
            "filter" : {
                "script" : {
                    "script" : {
                        "source": "doc['field2'].value.substring(0,9) == doc['field1'].value",
                        "lang": "painless"
                     }
                }
            },
            "must" : {
                "term" : { "field3" : "value3" }
            }
        }
    }
}

```

However this seems to give the same exact result as the same query without the substring(0,9).

```auto
{
    "query": {
        "bool" : {
            "filter" : {
                "script" : {
                    "script" : {
                        "source": "doc['field2'].value == doc['field1'].value",
                        "lang": "painless"
                     }
                }
            },
            "must" : {
                "term" : { "field3" : "value3" }
            }
        }
    }
}

```

Another approach was to use the find operaror `=~` but that doesn't seem to work either.

```auto
"source": "doc['field2'].value =~ doc['field1'].value"

```

it fails with

`java.lang.String cannot be cast to java.util.regex.Pattern.`

What I am actually trying to do, is transpose a solr join query to ES:

```auto
({!join from=field1 to=field2}field3:Value3)

```

any advice would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [November 17, 2017, 8:13am UTC](https://discuss.elastic.co/t/find-documents-where-field1-is-equal-to-substring-of-field2/108025/2 "2017-11-17T08:13:45Z")

</div>

You need to get the values from field2 in one query, then create a new query to search against field1 and in your client. There's no way to do that in Elasticsearch at the moment.

---

<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 15, 2017, 8:19am UTC](https://discuss.elastic.co/t/find-documents-where-field1-is-equal-to-substring-of-field2/108025/3 "2017-12-15T08:19:14Z")

</div>

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