How to sort nested documents

Elastic Stack Elasticsearch
1

Hi, I have been trying to sort nested documents but this solution https://www.elastic.co/guide/en/elasticsearch/reference/6.3/search-request-sort.html#nested-sorting is not what I need or at least couldn't come with my requirement for the sorting.
I need to sort the nested documents by a "date" field or "text" field inside this nested documents, subContent is my nested field.
Basically, I have something like the below structure, and I need to have the episodes for each series sorted by addDate (asc or desc)...Is it possible to do that in elastic search? working with ES 6.0.
MANY THANKS! Hope you can help me.

  {    
      {
          "id": "12345",
          "title": "The series ABC",
          "channelNumber": "074",
          "seasonNumber": "10",
          "section": "tvshows",
          "subContent": [
            {
              "id": "576849",
              "seasonNumber": "10",
              "addDate": "2018-06-03T18:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The series ABC episode 1",
              "episodeNumber": 1
            },
             {
              "id": "48595",
              "seasonNumber": "10",
              "addDate": "2018-07-01T20:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The series ABC episode 2",
              "episodeNumber": 2
            },
            {
              "id": "294904",
              "seasonNumber": "10",
              "addDate": "2018-07-09T20:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The series ABC episode 3",
              "episodeNumber": 3
            }
       },
       {
          "id": "423423",
          "title": "The series DEF",
          "channelNumber": "074",
          "seasonNumber": "05",
          "section": "tvshows",
          "subContent": [
            {
              "id": "79696",
              "seasonNumber": "10",
              "addDate": "2018-05-03T18:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The episode series DEF 1",
              "episodeNumber": 1
            },
             {
              "id": "3434465",
              "seasonNumber": "10",
              "addDate": "2018-07-08T20:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The episode series DEF 2",
              "episodeNumber": 2
            },
            {
              "id": "687888",
              "seasonNumber": "10",
              "addDate": "2018-07-09T20:53:02Z",
              "duration": "50 mins",
              "episodeTitle": "The episode series DEF 3",
              "episodeNumber": 3
            }
       }
    }
2

Yes it's possible!
Can we see your query please?

3

Hi! Thanks for your help!
This is my query:

POST content/_search  
{
   "query" : {
      "match_all" : {}
   },
   "sort" : [
       {
          "subContent.addDate" : {
             "order" : "desc",
             "nested": {
                "path": "subContent"
             }
          }
       }
    ]
}

In the response I can see the "sort" field with the value used to sort , which I think is correct. When the order is "desc" it chooses the highest value for addDate from the list of episodes, and when the order is "asc" it chooses the minimum value for addDate. The problem is that it is not sorting anything, the episodes come always in the same order as they were indexed. What is it that I am doing wrong?

Thanks in advance,
Celeste

4

Indeed, the sort clause on the top level is used to sort the documents, not the nested documents.
To achieve what you want, you need to add the inner_hits to be able to sort the nested documents, something like :

POST content/_search
{
  "query": {
    "nested": {
      "path": "subContent",
      "query": {
        "match_all": {}
      },
      "inner_hits": {
        "sort": [{ "subContent.addDate": "asc" }]
      }
    }
  }
}

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-inner-hits.html

5

Thanks Klof for your help.
The issue I am having now is that I have some documents which do not have subcontent nested field. I have a boolean must clause for the search of documents and adding this this new query for the nested sort as part of the boolean must queries, makes retrieve in the response only documents that have subContent field matching.
How can I retrieve all documents with/without nested SubContent (with sorted nested documents).
Thanks!!!!!

6

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