33_Nested_sorting.asciidoc

Sorting by Nested Fields

It is possible to sort by the value of a nested field, even though the value exists in a separate nested document. To make the result more interesting, we will add another record:

PUT /my_index/blogpost/2
{
  "title": "Investment secrets",
  "body":  "What they don't tell you ...",
  "tags":  [ "shares", "equities" ],
  "comments": [
    {
      "name":    "Mary Brown",
      "comment": "Lies, lies, lies",
      "age":     42,
      "stars":   1,
      "date":    "2014-10-18"
    },
    {
      "name":    "John Smith",
      "comment": "You're making it up!",
      "age":     28,
      "stars":   2,
      "date":    "2014-10-16"
    }
  ]
}

Imagine that we want to retrieve blog posts that received comments in October, ordered by the lowest number of stars that each blog post received. The search request would look like this:

GET /_search
{
  "query": {
    "nested": { (1)
      "path": "comments",
      "filter": {
        "range": {
          "comments.date": {
            "gte": "2014-10-01",
            "lt":  "2014-11-01"
          }
        }
      }
    }
  },
  "sort": {
    "comments.stars": { (2)
      "order": "asc",   (2)
      "mode":  "min",   (2)
      "nested_path": "comments", (3)
      "nested_filter": {
        "range": {
          "comments.date": {
            "gte": "2014-10-01",
            "lt":  "2014-11-01"
          }
        }
      }
    }
  }
}

1. The nested query limits the results to blog posts that received a comment in October.

2. Results are sorted in ascending (asc) order by the lowest value (min) in the comment.stars field in any matching comments.

3. The nested_path and nested_filter in the sort clause are the same as the nested query in the main query clause. The reason is explained next.

Why do we need to repeat the query conditions in the nested_path and nested_filter? The reason is that sorting happens after the query has been executed. The query matches blog posts that received comments in October, but it returns blog post documents as the result. If we didn’t include the nested_filter clause, we would end up sorting based on any comments that the blog post has ever received, not just those received in October.