How to write Elasticsearch multiple must scripts query?

Question

I want to use a query to compare multiple fields. I have field 1 to 4. I want to search data which field 1 is greater than field 2 and below query is work perfectly;

{
    "size": 0,
    "_source": [
        "field1",
        "field2",
        "field3",
        "field4"
    ],
    "sort": [],
    "query": {
        "bool": {
            "filter": [],
            "must": {
                "script": {
                    "script": {
                        "inline": "doc['field1'].value > doc['field2'].value;",
                        "lang": "painless"
                    }
                }
            }
        }
    }
}

Now, I want to search data which field 1 is greater than field 2 and also which field 3 is greater than field 4. according Elastic Search: How to write multi statement scripts? and This link I just need to separate each statement with a semicolon. So it should be like this:

{
    "size": 0,
    "_source": [
        "field1",
        "field2",
        "field3",
        "field4"
    ],
    "sort": [],
    "query": {
        "bool": {
            "filter": [],
            "must": {
                "script": {
                    "script": {
                        "inline": "doc['field1'].value > doc['field2'].value; doc['field3'].value > doc['field4'].value;",
                        "lang": "painless"
                    }
                }
            }
        }
    }
}

But that query doesn't work and return compile error like this:

{"root_cause":[{"type":"script_exception","reason":"compile error","script_stack":["doc['field1'].value > doc[' ...","^---- HERE"],"script":"doc['field1'].value > doc['field2'].value; doc['field1'].value > doc['field2'].value; ","lang":"painless"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"financials","node":"8SXaM2HcStelpLHvTDSMCQ","reason":{"type":"query_shard_exception","reason":"failed to create query: {\n \"bool\" : {\n \"must\" : [\n {\n \"script\" : {\n \"script\" : {\n \"source\" : \"doc['field1'].value > doc['field2'].value; doc['field1'].value > doc['field2'].value; \",\n \"lang\" : \"painless\"\n },\n \"boost\" : 1.0\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n}","index_uuid":"hz12cHg1SkGwq712n6BUIA","index":"financials","caused_by":{"type":"script_exception","reason":"compile error","script_stack":["doc['field1'].value > doc[' ...","^---- HERE"],"script":"doc['field1'].value > doc['field2'].value; doc['field1'].value > doc['field2'].value; ","lang":"painless","caused_by":{"type":"illegal_argument_exception","reason":"Not a statement."}}}}]}

Val · Accepted Answer · 2018-07-02 12:42:56Z

3

You need to combine your two conditions like this:

doc['field1'].value > doc['field2'].value && doc['field3'].value > doc['field4'].value
                                           ^
                                           |
                               replace the semicolon by &&

answered Jul 2, 2018 at 12:42

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muhammad Dyas Yaskur Over a year ago

wow, thank you so much, you are saved my live. actually that's on my mind before, but cause in filter query I cant use && so I think that would be doesn't work. I was take some hours to googling about the solution and I just got stressed. Evidently that which on my mind before is work fine.

Veverke Over a year ago

@Val: any idea of how to solve this ?

Paradise228 · Accepted Answer · 2018-07-02 12:45:15Z

-1

In order to use more than condition, 'must', 'should' and 'must_not' can be use as arrays, and each condition become on element of it. According to Elasticsearch documentation

"query": {
  "bool" : {
    "must" : {
      "term" : { "user" : "kimchy" }
    },
    "filter": {
      "term" : { "tag" : "tech" }
    },
    "must_not" : {
      "range" : {
        "age" : { "gte" : 10, "lte" : 20 }
      }
    },
    "should" : [
      { "term" : { "tag" : "wow" } },
      { "term" : { "tag" : "elasticsearch" } },
      { "term" : { "tag" : "and so on" } }
    ],
    "minimum_should_match" : 1,
    "boost" : 1.0
  }
}

answered Jul 2, 2018 at 12:45

Paradise228

7173 silver badges17 bronze badges

1 Comment

Val Over a year ago

The OP needs to compare two fields and you cannot do that with normal conditions, you need to use scripts or store the result of the comparison in a new field at indexing time.

Collectives™ on Stack Overflow

How to write Elasticsearch multiple must scripts query?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related