Issue with Nested Sorting in Opensearch Query

Elastic Stack Elasticsearch
1

Hi everyone,

I’m encountering an issue with sorting nested fields in an Elasticsearch query. Despite following the documentation, the sorting doesn’t seem to work as expected. I’m hoping someone can point out what I’m doing wrong.

Context:

I have an index with documents that include nested fields. Here’s an example document structure:

{
  "order_id": "12345",
  "products": [
    {
      "product_id": "A1",
      "delivery": {
        "date": "2023-04-20T08:30:00.000Z",
        "quantity": 10
      }
    },
    {
      "product_id": "B2",
      "delivery": {
        "date": "2023-04-22T09:45:00.000Z",
        "quantity": 5
      }
    }
  ]
}

I want to retrieve documents where a specific product_id exists and sort them based on the delivery.date field of that nested product in descending order.

Current Query:

Here is the query I’m using:

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "query": {
              "match": {
                "products.product_id": "B2"
              }
            },
            "path": "products"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "products.delivery.date": {
        "order": "desc",
        "mode": "max",
        "nested": {
          "path": "products",
          "filter": {
            "term": {
              "products.product_id": "B2"
            }
          }
        }
      }
    }
  ],
  "_source": {
    "includes": [
      "order_id",
      "products.product_id",
      "products.delivery.date",
      "products.delivery.quantity"
    ]
  }
}

Issue:

The query returns documents correctly but does not sort them as expected. Changing the sort order from desc to asc results in the same order, indicating that the sorting isn’t applied correctly.

3

I found the issue: my delivery object included both products and nested delivery objects. Therefore, while sorting, I need to specify both levels of nesting to sort based on products.delivery.date.