Find documents where field1 is equal to substring of field2

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.

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.

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