Sum by quantity and sort by lowest price

[brampurnot]

I have a requirement in Elasticsearch which I'm not able to implement at the moment. The use case is as follows; we have certain products uploaded in elastic (1 million + items) and each item has a quantity, a price and a lead time (for delivery).

Now I basically want to get the top matches (based on a product description search) where tot sum of all quantities = 1000 (example) sorted by the lowest price.

A similar but other query would be to get the top 1000 items with the lowest lead time.

Any recommendation on how to implement this and what the most performant way of doing this is?

Assume we have the following records:
Product 1 | Quantity 200 | price 4USD | lead time 2 days
Product 2 | Quantity 150 | price 3USD | lead time 5 days
Product 3 | Quantity 275 | price 5 USD | lead time 14

Now I want to get all products for a maximum of quantity of 200 with the cheapest items first. That would give me something like:
Product 2
Product 1

And then it would also give me some aggregates like the average delivery time for these 2 items is 3.5 days and total value is 650USD (150 x 3USD + 50 x 4 USD)

Thanks,
Bram

[79g (Daniel)]

Hello @brampurnot,

try these searches:

For the first query:

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

Second query:

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ],
  "aggs": {
    "lead_time_avg": {
      "avg": {
        "field": "lead_time"
      }
    },
    "total_price": {
      "sum": {
        "script": {
          "lang": "painless",
          "inline": "doc['price'].value * doc['quantity'].value"
        }
      }
    }
  }
}

Used data:

PUT /discuss
PUT /discuss/_mapping
{
  "properties": {
    "description": {
      "type": "text"
    },
    "quantity": {
      "type": "double"
    },
    "price": {
      "type": "double"
    },
    "lead_time": {
      "type": "double"
    }
  }
}

POST discuss/_doc/
{
  "description": "Product 1",
  "quantity": "200",
  "price": "4",
  "lead_time": "2"
}
POST discuss/_doc/
{
  "description": "Product 2",
  "quantity": "150",
  "price": "3",
  "lead_time": "5"
}
POST discuss/_doc/
{
  "description": "Product 3",
  "quantity": "275",
  "price": "5",
  "lead_time": "14"
}

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

I attach you also the output:

Hope it helps

[brampurnot]

Thanks for this @79g ! However I don't think this solves my use-case to be honest. I want to make sure that the sum of the "quantity" field doesn't exceed a specific value.

If I run this is my index with more than 300.000 products, then I'm getting back 7000 results because they all have a price that is lower than 200. However it doesn't take into account the sum of the quantity field.

[79g (Daniel)]

Hi,

@brampurnot

I want to make sure that the sum of the "quantity" field doesn't exceed a specific value.

I did not realize about that
For that you could add another condition in the search!

[brampurnot]

Yeah I tried multiple things but doesn't seem to be that simple. This is what I have now:

{
    "track_scores": true,
    "query": {
      "match_all": {}
    },
    "from": 0,
    "size": 10,
    "sort": [
        {
            "price": "asc"
        },
        "_score"
    ],
    "_source": [
        "description",
        "price",
        "lead_time",
        "quantity"
    ],
    "aggs": {
        "input_parent": {
            "terms": {
                "field": "price",
                "order": {
                    "_key": "asc"
                }
            },
            "aggs": {
                "limited_price": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('price', 0);state['my_hash'].put('docs', new ArrayList());",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['price']+=doc['price'].value;state['my_hash']['docs'].add(doc['price'].value);}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                },
                "limited_quality": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('quality', 0);",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['quality']+=doc['condition_state'].value;}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                },
                "limited_lead": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('lead_time', 0);",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['lead_time']+=doc['lead_time'].value;}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                }
            }
        }
    }
}