I have an index which I need to filter a multiplication of two fields to be within a range.

First, here's the mapping for my "items" index:

{
  "mappings": {
    "properties": {
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "price": {
        "type": "float"
      },
      "discount": {
        "type": "float"
      }
    }
  }
}

An item's actual price would be its price multiplied by its discount.

I need to create a query for items with their actual price to be between two numbers: X <= price * discount <= Y

I have looked at the documentation for Elasticsearch, but the range query seems to only take into account the value of a single field, not the multiplicative product of two fields:

{
  "query": {
    "range": {
      "price": { // only price
        "gte": 10, // X
        "lte": 200, // Y
      }
    }
  }
}

I wonder if there any solution besides adding another field which would store the multiplied value to be used in the query.

Thank you.

CodePudding user response：

You have 2 alternatives:

1. Add the field on index time
2. Use a runtime field

1 is self explaining, and it is the recommended one in most cases because storage is cheaper than compute. If you don't store it you will have to compute it every time.

2. You can use Runtime fields to generate this new field on the mappings, or in the query.

I will show you both ways:

mappings

PUT test_product
{
  "mappings": {
    "runtime": {
      "discount_price": {
        "type": "double",
        "script": {
          "source": "emit(doc['price'].value * doc['discount'].value )"
        }
      }
    },
    "properties": {
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "price": {
        "type": "double"
      },
      "discount": {
        "type": "double"
      }
    }
  }
}

Ingest a document

POST test_product/_doc
{
  "name": "Orange",
  "price": "10.0",
  "discount": "0.5"
}

Run a query:

GET test_product/_search
{
  "query": {
    "range": {
      "discount_price": {
        "gte": 5,
        "lte": 5
      }
    }
  }
}

Now without defining the runtime field in the mappings:

GET test_product/_search
{
  "runtime_mappings": {
    "discount_price": {
      "type": "double",
        "script": {
          "source": "emit(doc['price'].value * doc['discount'].value )"
        }
    }
  },
  "query": {
    "range": {
      "discount_price": {
        "gte": 5,
        "lte": 5
      }
    }
  }
}