Elasticsearch aggregations

[sofien.000]

I have documents in Elasticsearch1.5.2 that look like

POST /test/prod
{
    "title": "product 1, type1",
    "category": "category1",
    "price in €": 1.67,
    "price in €/kg": 3.35,
    "weight in kg":  0.500
}



POST /test/prod
{
    "title": "product 1, type2",
    "category": "category1",
    "price in €": 1.85,
    "price in €/kg": 1.85,
    "weight in kg":  1
}

POST /test/prod
{
    "title": "product 1, type3",
    "category": "category1",
    "price in €": 0.79,
    "price in €/kg": 4.7,
    "weight in kg": 0.168
}

POST /test/prod
{
    "title": "product 1, type4",
    "category": "category1",
    "price in €": 2.15,
    "price in €/kg": 4.3,
    "weight in kg": 0.5
}

I want to buy 1.2 kg of product1 by the cheapest price in € available, even by using combination between two or three types of product1.And at the same time, The sum of price in €/kg must be equal or greater than 1.200 kg. In my case I must have as a result product 1, type1 and product 1, type2.

My query looks like

POST test/_search?search_type=count
{
   "aggs" : {
        "product 1" : {
            "filter" : {
                "query":{ "query_string": {

                   "query": "title:product 1"
                }
            }},
            "aggs" : {
                "minprice": {
                    "top_hits": {
                        "sort": [
                            {
                                "price in €/kg": {
                                    "order": "asc"
                                }
                            }
                        ], "size":4
                    }
                }
            }
 }}   } 

I get my documents sorted by field price in €/kg. Now, I must range and sort the result of this query by the weight in kg from 1.2 kg. For example,

3 product 1, Type1 (0.5kg*3)> 1.2 kg. 
2 product 1, Type2 (1kg*2)>1.2kg. 
8 product 1,Type3 (0.168*8)>1.2kg. 
3 product 1, Type 4 (0.5*3)>1.2 kg. 
product 1, Type1 + product 1, Type2 (1+0.5)>1.2 
product 1, Type2 + 3 product 1, Type3 (1+(0.168*3))> 1.2
....
....

At the end, I want to keep as a final result the lowest price in € of all these combinations.

[Mark_Harwood]

I suspect much of your required logic will have to be implemented in your client and could involve more than one request to elasticsearch.

Elasticsearch cannot know what to do for example given these extreme scenarios when sorting by "price in €/kg":

1. Many small items: The first thousand products might weigh < 0.000001 kg (you asked for only the top 4 and a thousand of these still wouldn't give you the required weight).
2. A few large items: The cheapest product weighs 500Kg - this vastly exceeds your 1.2 kg size.

These are perhaps extreme examples but you can see why the business logic may dictate multiple requests or conditionals ("ignore product if individual weight > 110% of overall required weight").

If the weights we're talking about are cocaine I imagine you have some spare budget for developers...