Elasticsearch sorting by nested field in nested array

Question

I'm using ElasticSearch 7.2.1 and NEST 7.2.1

My data structure is following

{
  id: "some_id",
  "roles" : [
  {
    "name" : "role_one_name",
    "members" : [
    {
      "id" : "member_one_id",
      "name" : "member_one_name",
    }
    ]
  },
  {
    "name" : "role_two_name",
    "members" : [
    {
      "id" : "member_two_id",
      "name" : "member_two_name",
    }
    ]
  ]
}

The idea is that I need to implement sorting by given role name (e.g. role_one_name). Sorting should be performed on members.name (e.g. members[0].name). In my case members array will always contain one element, but for some roles (omitted in the example) it contains more that one element, so I can't get rid of nested array.

In my head I have an algorithm:

1. Get needed role by name.
2. Specify path to the first element in members array.
3. Point to the name property to sort on.

I'm a newbie in elasticsearch world, and after few days of trying I got a following query (which does not work).

var sortFilters = new List<Func<FieldSortDescriptor<T>, FieldSortDescriptor<T>>>(); 
var sortFieldValue = "role_two_name";
...

sortFilters.Add(o => o.Nested(n => n
    .Path(p => p.Roles)
    .Filter(f => f
        .Term(t => t
            .Field(c => c.Roles.First().Name)
            .Value(sortFieldValue)) && f
        .Nested(n => n
            .Path(p => p.Roles.First().Members)
            .Query(q => q
                .Term(t => t
                    .Field(f => f.Roles.First().Members.First().Name)))))));

What am I doing wrong?

Answer 1

With help of my colleagues I managed to solve it.

GET index_name/_search
{
 "from": 0,
 "size": 20,
  "query": {
    "match_all": {}
  },

  "sort": [{
      "roles.members.name.keyword": {
        "order": "asc",
        "nested": {
          "path": "roles",
          "filter": {
            "term": {
              "roles.name.keyword": {
                "value": "sortFieldValue"
              }
            }
          },
          "nested": {
            "path": "roles.members"
          }
        }
      }
    }
  ]
}

or using NEST:

sortFilters.Add(o => o.Field(f => f.Roles.First().Members.First().Name.Suffix("keyword")));

sortFilters.Add(o => o.Nested(n => n
    .Path(p => p.Roles)
    .Filter(f => f
        .Term(t => t
            .Field(q => q.Roles.First().Name.Suffix("keyword"))
            .Value(sortFieldValue)
        )
    )
    .Nested(n => n
        .Path(p => p.Roles.First().Members)
    )
));