Using scripted nested sorting with missing fields

Elastic Stack Elasticsearch
1

Hi,

I'm working with documents that looks like:

{
  "product_name": "abc",
  "prices": {
    "regular": 9.99,
    "pricing_tables": [
     { "id": 1, "price": 8.99 },
     { "id": 2, "price": 7.99 }
    ]
  }

Where prices.pricing_tables is a nested field.

What I want to do is sort with the following logic, given a pricing table id:

  • If the nested field contains the given id, use pricing_tables.price

  • If the nested field does not contain the id, use prices.regular

The query I tried to use so far:

"sort": [
{
  "_script": {
    "type": "number",
    "script": {
      "lang": "painless",
      "source": """
        if(doc['prices.pricing_tables.price'].size() != 0) { 
          return doc['prices.pricing_tables.price'].value; 
        } 
        else {
          return doc['prices.regular'].value;
        }
        """
    },
    "nested": {
      "path": "prices.pricing_tables",
      "filter": {
        "term": {"prices.pricing_tables.id": 1}
      }
    },
    "order": "asc"
  }
}  
]

However it does not work as expected. When there is no entries in the pricing_tables nested object, the sort value in the result is always 1.7976931348623157E308

What am I missing here? Is it possible to do this?

Thanks!

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.