Elasticsearch - Nested field sorting

Question

I have an index defined by the following :

    {
    "mappings": {
        "properties": {
            "firstName": {
                "type": "keyword"
            },
            "lastName": {
                "type": "keyword"
            },
            "affiliations": {
                "type": "nested",
                "properties": {
                    "organisation": {
                        "type": "keyword"
                    },
                    "team": {
                        "type": "keyword"
                    },
                    "dateBeginning": {
                        "type": "date",
                        "format": "yyyy-MM-dd"
                    },
                    "dateEnding": {
                        "type": "date",
                        "format": "yyyy-MM-dd"
                    },
                    "country": {
                        "type": "keyword"
                    }
                }
            }
        }
    }
    }

Basically, for each researcher (researchers is how I named my index) I want to sort the the affiliations by dateBeginning, in descending order. I've read about inner hits in the EL official doc, and not being exactly sure how it works I've tried this for researcher with _id : 3 :

    {
    "query": {
    "nested": {
        "path": "affiliations",
        "query": {
            "match": { "_id": 3 }
        },
        "inner_hits": {
            "sort" : [
            {
                "affiliations.dateBeginning" : {
                    "order" : "desc",
                    "nested": {
                        "path": "affiliations",
                        "filter": {
                            "term": { "_id": 3 }
                        }
                    }   
                }
            }
            ]
        } 
      }
      }
    }

And it doesn't really work.

Having two affiliation for researchers with _id : 3, with one dateBeginning set on 2015-06-30, and the other on 2017-06-30. So I've tried this also :

    {
    "sort" : [
            {
                "affiliations.dateBeginning" : {
                    "order" : "desc",
                    "nested": {
                        "path": "affiliations"
                }   
            }
        }
    ],
    "query": {
        "nested": {
            "path": "affiliations",
            "query": {
                "match": { "_id": 3 }
            }
        }
      }
    }

And it doesn't sort the affiliations by dateBeginning.

I've also tried to do it with the SQL API (since I'm more familiar with SQL language), and still, I can't get the data I want.

So I'm quite new to ElasticSearch, I'm using version 7.10, and I don't know what else to do.

Any suggestions about what I'm doing wrong here ?

EDIT

here's an example of a document from that index:

            {
            "took": 1,
            "timed_out": false,
            "_shards": {
                "total": 1,
                "successful": 1,
                "skipped": 0,
                "failed": 0
            },
            "hits": {
                "total": {
                    "value": 1,
                    "relation": "eq"
                },
                "max_score": 1.0,
                "hits": [{
                    "_index": "researchers",
                    "_type": "_doc",
                    "_id": "3",
                    "_score": 1.0,
                    "_source": {
                        "firstName": "Kimmich",
                        "lastName": "Yoshua",
                        "affiliations": [{
                                "organisation": "University of Ottawa",
                                "team": "Neural Network Elite Team",
                                "dateBeginning": "2015-06-30",
                                "datEnding": "2017-01-31",
                                "country": "Canada"
                            },
                            {
                                "organisation": "University of Montréal",
                                "team": "Picture processing team",
                                "dateBeginning": "2017-06-30",
                                "dateEnding": null,
                                "country": "Canada"
                            }
                        ]
                    }
                }]
            }
        }

question from:https://stackoverflow.com/questions/65641794/elasticsearch-nested-field-sorting

Answer 1

Once you're inside the nested query, the inner hits don't need the extra nested query. Remove it and the sort will work properly:

{
  "query": {
    "nested": {
      "path": "affiliations",
      "query": {
        "match": {
          "_id": 3
        }
      },
      "inner_hits": {
        "sort": [
          {
            "affiliations.dateBeginning": {
              "order": "desc"
            }
          }
        ]
      }
    }
  }
}

Note that this wouldn't sort the top-level hits -- only the inner hits. But you can sort on the top level by the values of affiliations.dateBeginning like so:

POST researchers/_search
{
  "sort": [
    {
      "affiliations.dateBeginning": {
        "order": "desc",
        "nested_path": "affiliations"
      }
    }
  ]
}

but note that the syntax is now slightly different: instead of path we're saying nested_path.