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

Just edited the post with a sample index doc

Dan
– Dan

2021-01-09 11:05:28 +00:00
Commented Jan 9, 2021 at 11:05 — Dan
– Dan, Commented Jan 9, 2021 at 11:05

Jozef - Spatialized.io · Accepted Answer · 2021-01-09 11:16:21Z

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.

answered Jan 9, 2021 at 11:16

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Working just fine. Thanks for the help!

Dan Over a year ago

Just one thing though : I think the syntax according to the official ES doc is actually path and the nested_path will be deprecated

Jozef - Spatialized.io Over a year ago

True true. I'm glad they deprecated it in favor of consistency.

Collectives™ on Stack Overflow

Elasticsearch - Nested field sorting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related