Graduated prices on documents

[Max_van_der_Sluis]

I'm having issues implementing graduated prices in Elasticsearch.
This is the mapping I use (I can change it, as long as it all stays in the same index):

PUT /sw_shop1_product/_mapping
    {
        "properties": {
        "cs_filter_prices": {
            "properties": {
                "price": {
                    "type": "double"
                },
                "from" : {
                    "type": "long"
                }
            },
            "type": "nested"
        }
      }
    }

So I'm adding multiple prices per document, I need to be able to filter based on amount and price.
The issue is, how can I get the correct documents based on the amount(highest amount that is lower/equal to input amount, and only that one) then also filter on a price ("lte").

Example with 2 products:

PUT sw_shop1_product/_doc/product_name_1
{ 
  "cs_filter_prices": [ 
    {
      "price": 1.50,
      "from": 1
    },
    {
      "price": 1.25,
      "from": 100
    },
    {
      "price": 1.00,
      "from": 200
    }
  ]
}

PUT sw_shop1_product/_doc/product_name_2
{ 
  "cs_filter_prices": [ 
    {
      "price": 2.50,
      "from": 1
    },
    {
      "price": 2.25,
      "from": 100
    },
    {
      "price": 2.00,
      "from": 200
    }
  ]
}

When I want to search for amount 125 and price "lte" 2.00, I should only get the document "product_name_1".
I have been banging my head against this issue for a bit of time, and I have no clue how to do this.

[mayya (Mayya Sharipova)]

You can use a nested query for that, something like this:

GET /sw_shop1_product/_search
{
    "query":  {
        "nested" : {
            "path" : "cs_filter_prices",
            "query" : {
                "bool" : {
                    "must" : [
                        { "range" : {"cs_filter_prices.from" : {"gt" : 125}} },
                        { "range" : {"cs_filter_prices.price" : {"lte" : 2.0}} }
                    ]
                }
            }
        }
    }
}

[Max_van_der_Sluis]

Yeah I had something similar but the issue is with the from amount, it's user input so it won't match exactly the from tiers.

{ "range" : {"cs_filter_prices.from" : {"gt" : 125}} }

Sorry, I'll be more exact:
I need it to match the highest from amount that is still lower then the requested amount or equal to it. so if from is 125 it would match the from 100 instead and only that one.
This is why I can't use "lte", or can I sort/filter the nested query instead to do what I want?

[mayya (Mayya Sharipova)]

oh I see now what you are trying to do. May be inner_hits in your case. For example, in this inner_hits you will see the highest from and only that one.

GET /sw_shop1_product/_search
{
  "query": {
    "nested": {
      "path": "cs_filter_prices",
      "query": {
        "bool": {
          "must": [
           { "range" : {"cs_filter_prices.from" : {"lte" : 125}} },
           { "range" : {"cs_filter_prices.price" : {"lte" : 2.0}} }
          ]
        }
      },
      "inner_hits": {
        "size": 1,
        "sort": [{"cs_filter_prices.from": "desc"}]
        }
      }
    }
  }
}

[Max_van_der_Sluis]

Thanks a bunch, I'm new to Elasticsearch and still wrapping my head around all these concepts, so connecting the dots is a bit hard for me.
I'll try it out and see if works. Will report back.