Comparing _routing with another field of document in script

Question

E.S Version: 5.5.2

Sample Document:

{
    "_index": "test_index",
    "_type": "doc",
    "_id": "5485044",
    "_score": 1,
    "_routing": "135767",
    "_source": {
      "e_id": 135767
    }
  }

Requirement:

To fetch all the document where-in _routing doesn't match with e_id

Query:

GET test_index/_search
{
  "size": 1000, 
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": "'_routing'!=doc['e_id'].value.toString()"
        }
      }
    }
  }

Outcome: The response I am getting is not what is expected. O/P docs with _routing == e_id are also getting matched.

Answer 1

It is not possible to access the _routing value in a search query.

However, what you can do is to first flag all documents whose e_id value differs from the _routing value and then query the flagged documents.

First, run an update by query to flag all documents

POST test_index/_update_by_query
{
  "script": {
    "source": "ctx._source.routingOk = (ctx._routing == ctx._source.e_id.toString())",
    "lang": "painless"
  }
}

Then query the documents which have routingOk: false:

GET test/_search?q=routingOk:false