0

I have an elasticsearch data where one field is derived as a combination of multiple fields as follows.

Field 1   Field 2   Field 3   Derived Field
a         b         c         a_b_c
b         c         a         b_c_a
c         a         b         c_a_b

i.e. Derived field = field1_field2_field3

I by mistake have created few records where derived field = field3_field2_field1

I want to write a query to get all records where derived field is not equal to field1_field2_field3.

I tried but I am not sure what could be the correct ES query for this.

Is it possible to get this data from ES. As per my understanding ES does not support joins like SQL. I am not sure if nested queries can help me here.

https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html

Gautam Jain
  • 33
  • 7

1 Answers1

0

Finally, after a bit of lookup, I was able to solve the issue by firing the following update_by_query script in ES.

    POST /<index_name>/_update_by_query
{
    "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "source": "String.valueOf(doc['field3'].value) + \"_\" + String.valueOf(doc['field2'].value) + \"_\"+ String.valueOf(doc['field1'].value) == String.valueOf(doc['derivedField'].value)",
            "lang": "painless"
          }
        }
      }
    }
  },
"script":{
    "source": " ctx._source['derivedField'] = ctx._source['field1'] + \"_\" + ctx._source['field2'] + \"_\" + ctx._source['field3'] ",
    "lang": "painless"
  }
}
Gautam Jain
  • 33
  • 7