Filtering in a Nested Sort appears to break sort

[wittjosiah (Josiah Witt)]

I've run into a weird issue with nested sorts. When specifying a sort as such:

"sort": [
    {
      "variations.price.regular": {
        "mode": "min",
        "order": "asc",
        "nested": {
          "path": "variations.price"
        }
      }
    }
  ]

It works as expected and sort values like "sort" : [ 500 ] are returned. However when specifying a nested sort with a filter as such:

"sort": [
    {
      "variations.price.regular": {
        "mode": "min",
        "order": "asc",
        "nested": {
          "path": "variations.price",
          "filter": {
            "term": {
              "variations.inventory.shop_id": 1
            }
          }
        }
      }
    }
  ]

It does not work as expected and sort values like "sort": [ 9223372036854775807 ] are returned.
Below is a full reproduction of this issue as a Kibana script:

DELETE /debug

PUT /debug

PUT /debug/_mapping/_doc
{
  "properties": {
    "name": {
      "type": "text"
    },
    "variations": {
      "type": "nested",
      "properties": {
        "name": {
          "type": "text"
        },
        "inventory": {
          "type": "nested",
          "properties": {
            "quantity": {
              "type": "integer"
            },
            "shop_id": {
              "type": "keyword"
            }
          }
        },
        "price": {
          "type": "nested",
          "properties": {
            "regular": {
              "type": "integer"
            },
            "shop_id": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

PUT /debug/_doc/1
{
  "name": "Product One",
  "variations": [
    {
      "name": "Product One - Variation One",
      "inventory": [
        {
          "quantity": 100,
          "shop_id": 1
        },
        {
          "quantity": 0,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 1000,
          "shop_id": 1
        },
        {
          "regular": 1000,
          "shop_id": 2
        }
      ]
    },
    {
      "name": "Product One - Variation Two",
      "inventory": [
        {
          "quantity": 0,
          "shop_id": 1
        },
        {
          "quantity": 100,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 2000,
          "shop_id": 1
        },
        {
          "regular": 2000,
          "shop_id": 2
        }
      ]
    }
  ]
}

PUT /debug/_doc/2
{
  "name": "Product Two",
  "variations": [
    {
      "name": "Product Two - Variation One",
      "inventory": [
        {
          "quantity": 0,
          "shop_id": 1
        },
        {
          "quantity": 100,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 3000,
          "shop_id": 1
        },
        {
          "regular": 3000,
          "shop_id": 2
        }
      ]
    },
    {
      "name": "Product Two - Variation Two",
      "inventory": [
        {
          "quantity": 0,
          "shop_id": 1
        },
        {
          "quantity": 100,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 4000,
          "shop_id": 1
        },
        {
          "regular": 4000,
          "shop_id": 2
        }
      ]
    }
  ]
}

PUT /debug/_doc/3
{
  "name": "Product Three",
  "variations": [
    {
      "name": "Product Three - Variation One",
      "inventory": [
        {
          "quantity": 100,
          "shop_id": 1
        },
        {
          "quantity": 100,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 500,
          "shop_id": 1
        },
        {
          "regular": 500,
          "shop_id": 2
        }
      ]
    },
    {
      "name": "Product Three - Variation Two",
      "inventory": [
        {
          "quantity": 0,
          "shop_id": 1
        },
        {
          "quantity": 0,
          "shop_id": 2
        }
      ],
      "price": [
        {
          "regular": 6000,
          "shop_id": 1
        },
        {
          "regular": 6000,
          "shop_id": 2
        }
      ]
    }
  ]
}

POST /debug/_search
{
  "query": {
    "nested": {
      "path": "variations",
      "query": {
        "bool": {
          "filter": [
            {
              "nested": {
                "path": "variations.inventory",
                "query": {
                  "bool": {
                    "filter": [
                      {
                        "term": {
                          "variations.inventory.shop_id": 1
                        }
                      },
                      {
                        "range": {
                          "variations.inventory.quantity": {
                            "gte": 1
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "sort": [
    {
      "variations.price.regular": {
        "mode": "min",
        "order": "asc",
        "nested": {
          "path": "variations.price",
          "filter": {
            "bool": {
              "filter": [
                {
                  "term": {
                    "variations.inventory.shop_id": 1
                  }
                },
                {
                  "range": {
                    "variations.inventory.quantity": {
                      "gte": 1
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  ]
}

POST /debug/_search
{
  "query": {
    "nested": {
      "path": "variations",
      "query": {
        "bool": {
          "filter": [
            {
              "nested": {
                "path": "variations.inventory",
                "query": {
                  "bool": {
                    "filter": [
                      {
                        "term": {
                          "variations.inventory.shop_id": 1
                        }
                      },
                      {
                        "range": {
                          "variations.inventory.quantity": {
                            "gte": 1
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "sort": [
    {
      "variations.price.regular": {
        "mode": "min",
        "order": "asc",
        "nested": {
          "path": "variations.price"
        }
      }
    }
  ]
}

Am I doing something wrong when adding the filter within the nested sort? The issue seems similar to Sort is out of order - using Nested Filter but there were no responses on that thread.

All tests run with Elasticsearch and Kibana 6.8.3 docker containers.

[wittjosiah (Josiah Witt)]

For any future forum wanderers that end up here, we ended up solving this by combining the two nested types into a single nested type. Below is a transformation of the above example similar to what we ended up doing:

DELETE /debug

PUT /debug

PUT /debug/_mapping/_doc
{
 "properties": {
   "name": {
     "type": "text"
   },
   "variations": {
     "type": "nested",
     "properties": {
       "name": {
         "type": "text"
       },
       "priceventory": {
         "type": "nested",
         "properties": {
           "inventory_quantity": {
             "type": "integer"
           },
           "regular_price": {
             "type": "integer"
           },
           "shop_id": {
             "type": "keyword"
           }
         }
       }
     }
   }
 }
}

PUT /debug/_doc/1
{
 "name": "Product One",
 "variations": [
   {
     "name": "Product One - Variation One",
     "priceventory": [
       {
         "inventory_quantity": 100,
         "regular_price": 1000,
         "shop_id": 1
       },
       {
         "inventory_quantity": 0,
         "regular_price": 1000,
         "shop_id": 2
       }
     ]
   },
   {
     "name": "Product One - Variation Two",
     "priceventory": [
       {
         "inventory_quantity": 0,
         "regular_price": 2000,
         "shop_id": 1
       },
       {
         "inventory_quantity": 100,
         "regular_price": 2000,
         "shop_id": 2
       }
     ]
   }
 ]
}

PUT /debug/_doc/2
{
 "name": "Product Two",
 "variations": [
   {
     "name": "Product Two - Variation One",
     "priceventory": [
       {
         "inventory_quantity": 0,
         "regular_price": 3000,
         "shop_id": 1
       },
       {
         "inventory_quantity": 100,
         "regular_price": 3000,
         "shop_id": 2
       }
     ]
   },
   {
     "name": "Product Two - Variation Two",
     "priceventory": [
       {
         "inventory_quantity": 0,
         "regular_price": 4000,
         "shop_id": 1
       },
       {
         "inventory_quantity": 100,
         "regular_price": 4000,
         "shop_id": 2
       }
     ]
   }
 ]
}

PUT /debug/_doc/3
{
 "name": "Product Three",
 "variations": [
   {
     "name": "Product Three - Variation One",
     "priceventory": [
       {
         "inventory_quantity": 100,
         "regular_price": 500,
         "shop_id": 1
       },
       {
         "inventory_quantity": 100,
         "regular_price": 500,
         "shop_id": 2
       }
     ]
   },
   {
     "name": "Product Three - Variation Two",
     "priceventory": [
       {
         "inventory_quantity": 0,
         "regular_price": 6000,
         "shop_id": 1
       },
       {
         "inventory_quantity": 0,
         "regular_price": 6000,
         "shop_id": 2
       }
     ]
   }
 ]
}

POST /debug/_search
{
 "query": {
   "nested": {
     "path": "variations",
     "query": {
       "bool": {
         "filter": [
           {
             "nested": {
               "path": "variations.priceventory",
               "query": {
                 "bool": {
                   "filter": [
                     {
                       "term": {
                         "variations.priceventory.shop_id": 1
                       }
                     },
                     {
                       "range": {
                         "variations.priceventory.inventory_quantity": {
                           "gte": 1
                         }
                       }
                     }
                   ]
                 }
               }
             }
           }
         ]
       }
     }
   }
 },
 "sort": [
   {
     "variations.priceventory.regular_price": {
       "mode": "min",
       "order": "asc",
       "nested": {
         "path": "variations.priceventory",
         "filter": {
           "bool": {
             "filter": [
               {
                 "term": {
                   "variations.priceventory.shop_id": 1
                 }
               },
               {
                 "range": {
                   "variations.priceventory.inventory_quantity": {
                     "gte": 1
                   }
                 }
               }
             ]
           }
         }
       }
     }
   }
 ]
}