How to do script sort on nested field with Elasticsearch?

Question

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?

Did you solve this eventually?

Shirkan
– Shirkan

2021-03-10 09:12:07 +00:00
Commented Mar 10, 2021 at 9:12 — Shirkan
– Shirkan, Commented Mar 10, 2021 at 9:12

Jozef - Spatialized.io · Accepted Answer · 2022-11-14 16:32:19Z

Your nested filter is essentially excluding all documents where pricing_tables.id isn't equal to 1. Elasticsearch then assigns the sort value of 1.79E308 do these documents. This number is not random -- it's indeed Double.MAX_VALUE and it's what ES will default to in case of an asc sort.

But back to your scripted sort. I don't think you can easily access the doc-values of prices.pricing_tables.price without making prices itself nested.

What you can do is:

access the underlying _source as a HashMap
iterate the pricing_tables (if applicable)
fall back to the regular price

Here's an example:

{
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "params": {
            "table_id": 1
          },
          "source": """
            def regular = doc['prices.regular'].value;
            def raw_prices = params._source['prices']['pricing_tables'];
          
            if (raw_prices.length != 0) { 
              for (def pair : raw_prices) {
                if (pair['id'] == params.table_id) {
                  return pair['price'];
                }
              }
              return regular; 
            }
            
            return regular;
          """
        },
        "order": "asc"
      }
    }
  ]
}

Keep in mind that accessing _source will make your sorting less performant than accessing doc-values.

Jimmy · Accepted Answer · 2022-11-21 20:55:58Z

The solution I found was not to use script sort at all.

I added the regular price in the pricing_tables nested field with id 0. Along with it I added an array with the ids of the other pricing_tables.

For example, if I need to sort with a specific pricing_tables value, I use:

{
  "prices.pricing_tables.price": {
    "mode": "min",
    "order": "asc",
    "nested": {
      "path": "prices.pricing_tables",
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "prices.pricing_tables.id": 1
              }
            },
            {
              "bool": {
                "must": {
                  "term": {
                    "prices.pricing_tables.id": 0
                  }
                },
                "must_not": {
                  "term": {
                    "prices.pricing_tables.other_tables": 1
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

Collectives™ on Stack Overflow

How to do script sort on nested field with Elasticsearch?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related