Is there a way to use script filelds calculation on filters using elasticsearch or opensearch API?

Question

I have an elasticsearch query that calculates price using script fileds. However I want to filter using calculated value but I cannot access it on script query. Is there a way I could use script field in filters?

I am using searchkick gem on ruby on rails app. I have following query for search

{
  script_fields: {
    total_stay_fee: {
      script: {
        source: "
          def totalStay = params.total_stay;
          def defaultRate = params['_source']['default_rate'];

          return totalStay * defaultRate;
        ",
        params: {
          total_stay: 12
        }
      }
    }
  },
  _source: ["*"],
  query: {
    function_score: {
      query: {
        bool: {
          filter: {
            range: {
              total_stay_fee: {
                gte: 12,
                lte: 100
              }
            }
          },
        }
      },
      boost_mode: "replace",
      score_mode: "first"
    }
  }
}

I have calculated value of total_stay_fee, however I want to filter result based on it's value. How do I achieve that?

Also, worth noting defaultRate is dynamic, so I cannot really index and use that either.

Answer 1

I think the most effective way would be to just divide values in gte and lte by 12 and compare with defaultRate.

However, assuming that you have a more complicated equation you can use script query. You cannot use _source in the script query because it will negatively affect performance on large datasets, but you can use doc values instead.

DELETE test
PUT test
{
  "mappings": {
    "properties": {
      "default_rate": {
        "type": "double"
      }
    }
  }
}

POST test/_bulk?refresh
{"index":{}}
{"default_rate":0.5}
{"index":{}}
{"default_rate":1}
{"index":{}}
{"default_rate":5}
{"index":{}}
{"default_rate":7}
{"index":{}}
{"default_rate":10}

POST test/_search
{
  "script_fields": {
    "total_stay_fee": {
      "script": {
        "source": """
          def totalStay = params.total_stay;
          def defaultRate = params['_source']['default_rate'];

          return totalStay * defaultRate;
        """,
        "params": {
          "total_stay": 12
        }
      }
    }
  },
  "_source": [
    "*"
  ],
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "source": """
                 def totalStay = params.total_stay;
                 def defaultRate = doc['default_rate'].value;
                 def total_stay_fee = totalStay * defaultRate;
                 return total_stay_fee >=  params.gte && total_stay_fee <=  params.lte;
              """,
              "params": {
                "total_stay": 12,
                "gte": 12,
                "lte": 100
              }
            }
          }
        }
      ]
    }
  }
}