Sorting of classes while getting top 10 classes by each category using aggregation

[Vaibhav_Vidhate]

We are using Elasticsearch for searching classes and showing a list of featured classes.
Each class can have many categories assigned. For each class, sort order is defined within each category assigned.
We need to retrieve top 10 classes by each category based on the sort order defined for classes within each category.

We have used Elasticsearch aggregation query and can retrieve the top 10 classes by each category but the class order is not correct.
While sorting, it seems to be applying sorting to all classes across categories and independent of category and then returning top 10 classes.
We need to sort classes within each category based on the sort order defined to classes within each category.
I am attaching a sample document structure and DSL used to get the list of classes.

{		  
 "fitnessClassId": 4193887,
 "name": "Dance Disco 20mins with Marcus #1",
 "description": "In this 20 minute groove class Marcus takes you on the journey of disco! You'll do classic disco moves intertwined with cardio grooves that will have you laughing and sweating the whole way.",
 "fitnessClassTypeId": 1,
 "videoId": 34494,
 "fitnessIntensityLevelId": 22,
 "totalRatings": 95,
 "rating": 4.34,
 "calories": 287,
 "createdById": 13253804,
 "createdDateUtc": "2020-09-25T02:54:31.2021647",
 "updatedDateUtc": "2022-12-06T19:33:20.5059525",
 "durationInMilliseconds": 1110037,
 "isIdle": false,
 "isDisconnectedClass": false,
 "isComplete": true,
 "isValidated": true,
 "fitnessClassType": {
   "fitnessClassTypeId": 1,
   "name": "Video"
 },
 "fitnessIntensityLevel": {
   "fitnessIntensityLevelId": 22,
   "name": "Standard",
   "description": "Standard",
   "sortOrder": 2
 },
 "languageTag": {
   "languageTagId": 1,
   "tag": "en",
   "description": "English"
 },
 "fitnessClassCategories": [
	{
     "fitnessCategoryId": 150,
     "sortOrder": 2
   },
	{
     "fitnessCategoryId": 154,
     "sortOrder": 5
   },
   {
     "fitnessCategoryId": 156,
     "sortOrder": 7
   }
 ],
 "virtualInstructors": [
   {
     "virtualInstructorId": 9465,
     "firstName": "Marcus",
     "lastName": "Irwin "
   }
 ]
}

DSL for getting the list of classes by category:

POST /fitnessclass/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "zones",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "zones.zoneId": 104348
                    }
                  },
                  {
                    "term": {
                      "zones.isSubscribedToCurrentZone": true
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_category": {
      "terms": {
        "field": "fitnessClassCategories.fitnessCategoryId",
        "size": 25,
        "order": {
          "_key": "asc"
        }
      },
      "aggs": {
        "top_docs": {
          "top_hits": {
            "size": 10,
             "sort":
				[
                 {
                   "fitnessClassCategories.fitnessCategoryId": {
                     "order": "asc"
                   }
                 },
                   {
                   "fitnessClassCategories.sortOrder": {
                     "order": "asc"
                   }
                 }
               ]
          }
        }
      }
    }
  },
  "size": 0
}

[mavwolverine (Viraj Kanwade)]

Since fitnessClassCategories is array of objects, you need to use nested and then reverse_nested aggregations.

POST /fitnessclass/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "zones",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "zones.zoneId": 104348
                    }
                  },
                  {
                    "term": {
                      "zones.isSubscribedToCurrentZone": true
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "fitness-class-categories": {
      "nested": {
        "path": "fitnessClassCategories"
      },
      "aggs": {
        "group-by-category": {
          "terms": {
            "field": "fitnessClassCategories.fitnessCategoryId",
            "order": {
              "_key": "asc"
            }
          },
          "aggs": {
            "top-classes-by-category": {
              "reverse_nested": {},
              "aggs": {
                "top-classes": {
                  "top_hits": {
                    "size": 10,
                    "sort": [
                      {
                        "fitnessClassCategories.customSortOrder": {
                          "order": "asc",
                          "nested": {
                            "path": "fitnessClassCategories"
                          }
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "size": 0
}