CODEWITHSUNDEEP
elasticsearch
Alen Jacob
Alen Jacob

Reputation: 170

Search in ElasticSearch with where condition

Let's say I have documents with the following fields: {field1, field2, costprice, sellingprice}

I need to run some queries where some of the conditions will require the difference between the costprice and the sellingprice

In standard SQL, an example could be:

SELECT * FROM Table1 where costprice-sellingprice>0

How can I achieve this in elasticsearch?

Upvotes: 1

Views: 679

Answers (2)

schellingerht
schellingerht

Reputation: 5796

You can do it with script filter, like so.

How to do your condition

{
    "query": {
        "filtered": {
            "query": {
                "match_all": {}
            },
            "filter": {
                "bool": {
                    "must": {
                        "script": {
                            "script": "(Float.parseFloat(doc['costprice'].value) - Float.parseFloat(doc['sellingprice'].value) > 0"
                        }
                    }
                }
            }
        }
    }
}

Note that dynamic scripting is disabled by default

Groovy dynamic scripting off by default from v1.4.3 Groovy dynamic scripting is off by default, preventing dynamic Groovy scripts from being accepted as part of a request or retrieved from the special .scripts index. You will still be able to use Groovy scripts stored in files in the config/scripts/ directory on every node. (https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html)

Elasticsearch has disabled dynamic scripts by default. Of cource, you can enable it in the config, but it's better to use indexed scripts. It's simple to put your script in price.groovy in the scripts directory of Elasticsearch. Elasticsearch will index your script, so you can use it als follows:

{
    "query": {
        "filtered": {
            "query": {
                "match_all": {}
            },
            "filter": {
                "bool": {
                    "must": {
                       "script": {
                           "file": "price",
                           "lang": "groovy"
                        }
                    }
                }
            }
        }
    }
}

Your price.groovy file looks like:

(Float.parseFloat(doc['costprice'].value) - Float.parseFloat(doc['sellingprice'].value) > 0;

Upvotes: 1

Andrei Stefan
Andrei Stefan

Reputation: 52368

Still scripts is what you need:

{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        }
      ],
      "filter": {
        "script": {
          "script": "doc['costprice'].value - doc['sellingprice'].value > 0"
        }
      }
    }
  }
}

Upvotes: 3

Related Questions