Elastic search dynamic field mapping with range query on price field

Question

I have two fields in my elastic search which is lowest_local_price and lowest_global_price.

I want to map dynamic value to third field price on run time based on local or global country.

If local country matched then i want to map lowest_local_price value to price field.

If global country matched then i want to map lowest_global_price value to price field.

If local or global country matched then i want to apply range query on the price field and boost that doc by 2.0.

Note : This is not compulsary filter or query, if matched then just want to boost the doc.

I have tried below solution but does not work for me.

Query 1:

$params["body"] = [
            "runtime_mappings" => [
                "price" => [
                    "type" => "double",
                    "script" => [
                        "source" => "if (params['_source']['country_en_name'] == '$country_name' ) { emit(params['_source']['lowest_local_price']); } else { emit( params['_source']['global_rates']['$country->id']['lowest_global_price']); }"
                    ]
                ]
            ],
            "query" => [
                "bool" => [
                    "filter" => [
                        "range" => [ "price" => [ "gte" => $min_price]]
                    ],
                    "boost" => 2.0
                ]
            ]
        ];

Query 2:

$params["body"] = [
            "runtime_mappings" => [
                "price" => [
                    "type" => "double",
                    "script" => [
                        "source" => "if (params['_source']['country_en_name'] == '$country_name' ) { emit(params['_source']['lowest_local_price']); } else { emit( params['_source']['global_rates']['$country->id']['lowest_global_price']); }"
                    ]
                ]
            ],
            "query" => [
                "bool" => [
                    "filter" => [
                        "range" => [ "price" => [ "gte" => $min_price, "boost" => 2.0]]
                    ],
                    
                ]
            ]
        ];

None of them working for me, because it can boost the doc. I know filter does not work with boost, then what is the solution for dynamic field mapping with range query and boost?

Please help me to solve this query.

Thank you in advance!

Answer 1

You can (most likely) achieve what you want without runtime_mappings by using a combination of bool queries, here's how.

Let's define test mapping

We need to clarify what mapping we are working with, because different field types require different query types.

Let's assume that your mapping looks like this:

PUT my-index-000001
{
  "mappings": {
    "dynamic": "runtime",
    "properties": {
      "country_en_name": {
        "type": "text"
      },
      "lowest_local_price": {
        "type": "float"
      },
      "global_rates": {
        "properties": {
          "UK": {
            "properties":{
              "lowest_global_price": {
                "type": "float"
              }
            }
          },
          "FR": {
            "properties":{
              "lowest_global_price": {
                "type": "float"
              }
            }
          },
          "US": {
            "properties":{
              "lowest_global_price": {
                "type": "float"
              }
            }
          }
        }
      }
    }
  }
}

Note that country_en_name is of type text, in general such fields should be indexed as keyword but for the sake of demonstration of the use of runtime_mappings I kept it text and will show later how to overcome this limitation.

bool is the same as if for Elasticsearch

The query without runtime mappings might look like this:

POST my-index-000001/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "match_all": {}
        },
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "country_en_name": "UK"
                      }
                    },
                    {
                      "range": {
                        "lowest_local_price": {
                          "gte": 1000
                        }
                      }
                    }
                  ]
                }
              },
              {
                "range": {
                  "global_rates.UK.lowest_global_price": {
                    "gte": 1000
                  }
                }
              }
            ],
            "boost": 2
          }
        }
      ]
    }
  }
}

This can be interpreted as the following:

Any document
OR (
  (document with country_en_name=UK AND lowest_local_price > X)
  OR
  (document with global_rates.UK.lowest_global_price > X)
)[boost this part of OR]

The match_all is needed to return also documents that do not match the other queries.

How will the response of the query look like?

Let's put some documents in the ES:

POST my-index-000001/_doc/1
{
  "country_en_name": "UK",
  "lowest_local_price": 1500,
  "global_rates": {
    "FR": {
      "lowest_global_price": 1000
    },
    "US": {
      "lowest_global_price": 1200
    }
  }
}

POST my-index-000001/_doc/2
{
  "country_en_name": "FR",
  "lowest_local_price": 900,
  "global_rates": {
    "UK": {
      "lowest_global_price": 950
    },
    "US": {
      "lowest_global_price": 1500
    }
  }
}

POST my-index-000001/_doc/3
{
  "country_en_name": "US",
  "lowest_local_price": 950,
  "global_rates": {
    "UK": {
      "lowest_global_price": 1100
    },
    "FR": {
      "lowest_global_price": 1000
    }
  }
}

Now the result of the search query above will be something like:

{
...
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 4.9616585,
    "hits" : [
      {
        "_index" : "my-index-000001",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 4.9616585,
        "_source" : {
          "country_en_name" : "UK",
          "lowest_local_price" : 1500,
          ...
        }
      },
      {
        "_index" : "my-index-000001",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 3.0,
        "_source" : {
          "country_en_name" : "US",
          "lowest_local_price" : 950,
          "global_rates" : {
            "UK" : {
              "lowest_global_price" : 1100
            },
            ...
          }
        }
      },
      {
        "_index" : "my-index-000001",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "country_en_name" : "FR",
          "lowest_local_price" : 900,
          "global_rates" : {
            "UK" : {
              "lowest_global_price" : 950
            },
            ...
          }
        }
      }
    ]
  }
}

Note that document with _id:2 is on the bottom because it didn't match any of the boosted queries.

Will runtime_mappings be of any use?

Runtime mappings are useful in case there's an existing mapping with data types that do not permit to execute a certain type of query. In previous versions (before 7.11) one would have to do a reindex in such cases, but now it is possible to use runtime mappings (but the query is more expensive).

In our case, we have got country_en_name indexed as text which is suited for full-text search and not for exact lookups. We should rather use keyword instead. This is how the query may look like with the help of runtime_mappings:

POST my-index-000001/_search
{
  "runtime_mappings": {
    "country_en_name_keyword": {
      "type": "keyword",
      "script": {
        "source": "emit(params['_source']['country_en_name'])"
      }
    }
  },
  "query": {
    "bool": {
      "should": [
        {
          "match_all": {}
        },
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "country_en_name_keyword": "UK"
                      }
                    },
                    {
                      "range": {
                        "lowest_local_price": {
                          "gte": 1000
                        }
                      }
                    }
                  ]
                }
              },
              {
                "range": {
                  "global_rates.UK.lowest_global_price": {
                    "gte": 1000
                  }
                }
              }
            ],
            "boost": 2
          }
        }
      ]
    }
  }
}

Notice how we created a new runtime field country_en_name_keyword with type keyword and used a term lookup instead of match query.