Filtering in a Nested Sort appears to break sort

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.

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

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