Find documents where field1 is equal to substring of field2

Elastic Stack Elasticsearch
1

Hello,

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

{
    "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).

{
    "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.

"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:

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

any advice would be greatly appreciated.

2

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.