Filter nested sorting in elasticsearch

Question

I have a document with a nested structure the nested object has an assignment_name and a due_date:

The mapping

{
  "goal": {
    "mappings": {
      "doc": {
        "properties": {
          "title": {
            "type": "keyword"
          },
          // lot's of other fields here ...
          "steps": {
            "type": "nested",
            "properties": {
              "assignment_name": {
                "type": "keyword"
              },
              "due_date": {
                "type": "date"
              }
              // lots of other fields here
            }
          }
        }
      }
    }
  }
}

I want to:

Filter all document that have a specific assignment_name (e.g.user_a)
Sort the result by the next due_date, not taking other assignements into account.

This query gives me random result (no sortings):

{  
   "query":{  
      "bool":{  
         "filter":[  
            {  
               "nested":{  
                  "path":"steps",
                  "query":{  
                     "term":{  
                        "steps.assignment_name":"user_a"
                     }
                  }
               }
            }
         ]
      }
   },
   "sort":[  
      {  
         "steps.due_date":{  
            "order":"asc",
            "nested":{  
               "path":"steps",
               "filter":{  
                  "term":{  
                     "steps.assignment_name":"user_a"
                  }
               }
            }
         }
      }
   ],
   "from":0,
   "size":25
}

Nishant · Accepted Answer · 2019-06-26 10:17:02Z

2

Firstly you need to ensure that datatype for steps field is nested. Then you have to use nested sorting to sort documents based on a nested document field.

The query would be:

{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "steps",
            "query": {
              "term": {
                "steps.assignment_name": "user_a"
              }
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "steps.due_date": {
        "order": "asc",
        "nested": {
          "path": "steps",
          "filter": {
            "term": {
              "steps.assignment_name": "user_a"
            }
          }
        }
      }
    }
  ]
}

The catch above is to use the same filter in sort as used in the main query to filter the documents. This ensures that the correct nested document's field value is considered to sort the documents.

edited Jun 26, 2019 at 10:17

answered Jun 26, 2019 at 3:14

Nishant

7,9121 gold badge24 silver badges37 bronze badges

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

14 Comments

shredding Over a year ago

Hm. That does not work. I get zero results with the .keyword postfix and random (not sorted) results without it.

Nishant Over a year ago

@shredding Can you add mapping of index to the question?

shredding Over a year ago

The actual mapping is somewhat different (as the problem was easier to explain with comments, but i will update accordingly) in a sec!

Nishant Over a year ago

Seems like you are querying before the index gets refreshed.

Nishant Over a year ago

Look at refresh index. This should be used with care for production cluster. Though its ok to use for dev/test env and UTs.

|

Collectives™ on Stack Overflow

Filter nested sorting in elasticsearch

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related