How to add between filter on nested script_score?

Question

I'm filtering prices dynamically with the given currency rates and sorting them with the score which is generated by script. But there is one thing I could not figure out how to do is range filter.

For example I only want to get product_platforms only match score between 10 and 100.

Index request.

PUT /test_products
{
  "settings": {
      "number_of_shards": 3,
      "number_of_replicas": 0,
      "analysis": {
        "filter": {
          "autocomplete_filter": {
            "type": "edge_ngram",
            "min_gram": "2",
            "max_gram": "15"
          }
        },
        "analyzer": {
          "autocomplete": {
            "type": "custom",
            "tokenizer": "standard",
            "filter": [
              "lowercase",
              "autocomplete_filter"
            ]
          }
        }
      }
    },
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword",
        "doc_values": true
      },
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword"
          },
          "raw": {
            "type": "keyword"
          }
        },
        "analyzer": "autocomplete",
        "search_analyzer": "standard"
      },
      "product_platforms": {
        "type": "nested",
        "properties": {
          "id": {
            "type": "long"
          },
          "platform_id": {
            "type": "long"
          },
          "price": {
            "type": "float"
          },
          "currency_id": {
            "type": "long"
          },
          "currency_code": {
            "enabled": false
          },
          "sku": {
            "type": "keyword"
          },
          "quantity": {
            "type": "long"
          }
        }
      }
    }
  }
}

Insert test documents:

POST /test_products/_bulk?pretty&refresh
{"index":{"_id": 1}}
{"id": 1, "name": "1. Product", "product_platforms": [{"id": 11, "platform_id": 3, "price": 100, "currency_id": 1, "currency_code": "TRY", "sku": "URN_1_1", "quantity": 1},{"id": 12, "platform_id": 3, "price": 75, "currency_id": 2, "currency_code": "USD", "sku": "URN_1_2", "quantity": 1},{"id": 13, "platform_id": 2, "price": 15, "currency_id": 2, "currency_code": "USD", "sku": "URN_1_3", "quantity": 1}]}
{"index":{"_id": 2}}
{"id": 2, "name": "2. Product", "product_platforms": [{"id": 21, "platform_id": 3, "price": 50, "currency_id": 1, "currency_code": "TRY", "sku": "URN_2_1", "quantity": 1},{"id": 22, "platform_id": 3, "price": 25, "currency_id": 2, "currency_code": "USD", "sku": "URN_2_2", "quantity": 1},{"id": 23, "platform_id": 3, "price": 75, "currency_id": 1, "currency_code": "TRY", "sku": "URN_2_3", "quantity": 1}, {"id": 24, "platform_id": 3, "price": 20, "currency_id": 2, "currency_code": "USD", "sku": "URN_2_4", "quantity": 1}]}

And here is the my search query:

GET /test_products/_search
{
  "query": {
    "nested": {
      "path": "product_platforms",
      "score_mode": "max",
      "query": {
        "function_score": {
          "query": {
            "bool": {
              "must": [
                {
                  "term": {
                    "product_platforms.platform_id": {
                      "value": "3"
                    }
                  }
                }
              ]
            }
          },
          "boost_mode": "replace",
          "script_score": {
            "script": {
              "source": """

              doc['product_platforms.price'].value * (doc['product_platforms.currency_id'].value == 2 ? params.rate_usd : (doc['product_platforms.currency_id'].value == 3 ? params.rate_eur : params.rate_try))         """,
              "params": {
                "rate_try": 1,
                "rate_usd": 7,
                "rate_eur": 8
              }
            }
          }
        }
      },
      "inner_hits": {
        "name": "product_platforms",
        "_source": true,
        "size": 5,
        "sort": {
          "_script": {
            "type": "number",
            "script": {
              "lang": "painless",
              "source": """                 doc['product_platforms.price'].value * (doc['product_platforms.currency_id'].value == 2 ? params.rate_usd : (doc['product_platforms.currency_id'].value == 3 ? params.rate_eur : params.rate_try))               """,
              "params": {
                "rate_try": 1,
                "rate_usd": 7,
                "rate_eur": 8
              }
            },
            "order": "desc"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ]
}

I'm using version 7.10 btw.

Answer 1

You could repeat that score calculator once again, this time in a boolean script query of its own.

Now, since your currency conversion script repeats itself one too many times, you could store it and reference it by its ID every time you need it. You'll of course keep the rates parametrized but the whole thing will be a bit more readable and maintainable.

So, let's save the script first:

POST _scripts/product-platforms-converter
{
  "script": {
    "source": """
      def price = doc['product_platforms.price'].value;
      def currency_id = doc['product_platforms.currency_id'].value;
    
      def converted_price = price * (currency_id == 2 
            ? params.rate_usd : (currency_id == 3 
                ? params.rate_eur : params.rate_try)); 
              
      if (params.final_range != null) {
        def is_in_range = converted_price >= params.final_range.gte 
            && converted_price <= params.final_range.lte;
        
        return is_in_range;
      }
      
      return converted_price;
      """,
    "lang": "painless"
  }
}

Notice that if final_range is provided in the params, the script returns a boolean; if not, it'll simply return the converted_price.

After that, the original query can be rewritten as:

GET /test_products/_search
{
  "query": {
    "nested": {
      "path": "product_platforms",
      "score_mode": "max",
      "query": {
        "function_score": {
          "query": {
            "bool": {
              "must": [
                {
                  "term": {
                    "product_platforms.platform_id": {
                      "value": "3"
                    }
                  }
                },
                {
                  "script": {
                    "script": {
                      "id": "product-platforms-converter",
                      "params": {
                        "rate_try": 1,
                        "rate_usd": 7,
                        "rate_eur": 8,
                        "final_range": {             <--- the desired "range" query
                          "gte": 10,
                          "lte": 100
                        }
                      }
                    }
                  }
                }
              ]
            }
          },
          "boost_mode": "replace",
          "script_score": {
            "script": {
              "id": "product-platforms-converter",
              "params": {
                "rate_try": 1,
                "rate_usd": 7,
                "rate_eur": 8
              }
            }
          }
        }
      },
      "inner_hits": {
        "name": "product_platforms",
        "_source": true,
        "size": 5,
        "sort": {
          "_script": {
            "type": "number",
            "script": {
              "id": "product-platforms-converter",
              "params": {
                "rate_try": 1,
                "rate_usd": 7,
                "rate_eur": 8
              }
            },
            "order": "desc"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ]
}