Sorting based on Nested aggregation values

My mapping is as follows
#------------------------------------------------------------
POST /userinfo
{
"mappings": {

  "skillinfo": {
    "properties": {
      "keyskills": {
              "type": "nested",
       "include_in_parent": true,
       "properties": {
        "rating": {
         "type": "long"
         },
         "skill": {
            "type": "string"           
          }
        }
      },
      "username": {
        "type": "string",
        "index": "not_analyzed"
      }
    }
  }
}

}
#-------------------------------------------------
A sample doc in the index is as follows..(only _source part is included)
"_source": {

      "username": "Arathi",
      "keyskills": [
        {
          "skill": "java",
          "rating": 4
        },
        {
          "skill": "php",
          "rating": 2
        }
      ]
    }
  },

    "_source": {
      "username": "Jithin",
      "keyskills": [
        {
          "skill": "java",
          "rating": 2
        },
        {
          "skill": "php",
          "rating": 3
        }
      ]
    }
  },
            ...

Here, I want to sort records based on sum of rating.
ie, if some one specifies java and php as skills, the rating of php and java is summed for each document.
and i want to sort documents based on this sum.
for more clarification, in the above example,
if some one specifies 'php' as required skill,
then documents will be sorted as
-> Jithin (sum of rating : 3)
-> Arathi (sum of rating: 2)
if someone specifes 'java and php' as required skill,
then the order will be.
-> Arathi (sum of rating = 4(java) + 2(php) )
-> Jithin (sum of rating = 2(java) + 3(php) )

and my query is as follows..

POST /userinfo/_search
{
"query": {

"match": {
  "keyskills.skill": "java php"
}

},
"aggs": {

"group_by_username": {
  "terms": {
    "field": "username",
    "size": 10,
    "order": {
      "total_rating": "desc"
    }
  },
  "aggs": {
    "nested_agg": {
      "nested": {
        "path": "keyskills"
      },
       "aggs": {
         "filter_doc": {
          "filters": {
             "filters": {
               "skill_filter": {
                "query": {
                  "match": {
                    "keyskills.skill": "java php"
                  }
                 }
               }
             }
           }, 
            "aggs": {
            "total_rating": {
              "sum": {
                "field": "keyskills.rating"
              }
            }
          }
        }
      }
    }
  }
}

}
}

The query works fine.. but i can not sort based on the 'total_rating' aggregation.
--> is there a way to sort based on total_rating with this query???
--> if not, is there way to do this better?

for reference, query results are shown below : (Aggregations part is shown )

"aggregations": {

"group_by_username": {
  "doc_count_error_upper_bound": 0,
  "sum_other_doc_count": 0,
  "buckets": [
    {
      "key": "Arathi",
      "doc_count": 1,
      "nested_agg": {
        "doc_count": 2,
        "filter_doc": {
          "buckets": {
            "skill_filter": {
              "doc_count": 2,
              "total_rating": {
                "value": 6
              }
            }
          }
        }
      }
    },
    {
      "key": "Jithin",
      "doc_count": 1,
      "nested_agg": {
        "doc_count": 2,
        "filter_doc": {
          "buckets": {
            "skill_filter": {
              "doc_count": 2,
              "total_rating": {
                "value": 5
              }
            }
          }
        }
      }
    },
  ...

Thanks in advance. :slight_smile:

1 Like