How to do script sort on nested field with Elasticsearch?

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?

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
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}