Accessing value from first array element - filtered

Question

After spent weekend looking for answer I decided to ask you for help. I have an index with products. Variants are nested list of objects in the products and i sort them by stock quantity and some other conditions while feeding index. I'm tryin to sort products by value of first variant in elastic resposne (including filters passed in query). Here is a example what i'm looking for. Shortened elastic response structure:

{
    "id": 6346,
    "name": "name",
    "variants": [
        {
            "variant_id": 1,
            "stock_quantity": 20,
            "price": 9.9
        },
        {
            "variant_id": 2,
            "stock_quantity": 15,
            "price": 20.9
        },
        {
            "variant_id": 3,
            "stock_quantity": 0,
            "price": 16.9
        }
    ]
}

Let's filter list by price gte 15:

{
    "id": 6346,
    "name": "name",
    "variants": [
        {
            "variant_id": 2,
            "stock_quantity": 15,
            "price": 20.9
        },
        {
            "variant_id": 3,
            "stock_quantity": 0,
            "price": 16.9
        }
    ]
}

Now i try to sort products by price of first variant from response. What have i tried to do so far?

Sort by script

"sort": 
     {
       "_script": {
         "order": "desc",
         "type": "number",
         "script": {
          "source": "return params['_source']['variants'][0].gross_price"
         }
       }
     }

That worked until i started filtering the list. In variants.gross_price gte 15 case, script returns value from the source document: 9.9, not 20.9 as I expected. I'm skipping the fact that ref the source is not recommended for performance reasons.

Sort by field

"sort": 
{
      "variants.gross_price": {
        "nested": {
          "path": "variants",
          "filter": {
            "bool": {
              "filter": [               
                {
                  "range": {
                    "variants.gross_price": {
                      "gt": "15"
}
[...]

I don't know why but this way i get no price of first variant from the response but the price of cheapest variant in product - 16.9 in this case.

I also added flag 'include_in_parent' to variants and try to get value by "doc['variants.gross_price'].values" in script, but same effect as first one.

So.. What else could i try to do?

UPDATE Max_children from 6.5 release is what i'm looking for.

Answer 1

Here we are, please use java max value instead of 999 and you should be ok ;). You have to loop on id to find the min with a gross price >15. And return the gross price assiociated. Please tell us if it works, i tested on my data and seems to be ok.

{
  "query": {
    "nested": {
      "path": "variants",
      "query": {
        "range": {
          "variants.gross_price": {
            "gt": "15"
          }
        }
      }
    }
  },
  "sort": [
    {
      "_script": {
        "script": {
          "inline": " int minima = 999; long result = -1; if (doc['variants.gross_price'].value > 15) { if (doc['variants.variant_id'].value < minima) minima = doc['variants.variant_id'].value; result = doc['variants.gross_price'].value;} \n return result",
          "lang": "painless"
        },
        "order": "desc",
        "nested_path": "variants",
        "type": "number"
      }
    }
  ],
  "from": 0,
  "size": 10
}

Answer 2

You can try the below script.

"sort": {
        "_script": {
            "type": "number",
            "script": {
                "inline": "if(doc['variants.gross_price'].value > 15) {max = doc['variants.gross_price'].value;}, abs(max)",
            "order" : asc
            }
       }
   }