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

**URL:** <https://discuss.elastic.co/t/sorting-of-classes-while-getting-top-10-classes-by-each-category-using-aggregation/352620>\
**Category:** Elasticsearch\
**Tags:** aggregations\
**Created:** [February 6, 2024, 11:10am UTC](https://discuss.elastic.co/t/sorting-of-classes-while-getting-top-10-classes-by-each-category-using-aggregation/352620 "2024-02-06T11:10:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vaibhav\_Vidhate](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vaibhav_vidhate/32/123510_2.png) [@Vaibhav\_Vidhate](https://discuss.elastic.co/u/Vaibhav_Vidhate)\
**Post date:** [February 6, 2024, 11:10am UTC](https://discuss.elastic.co/t/sorting-of-classes-while-getting-top-10-classes-by-each-category-using-aggregation/352620/1 "2024-02-06T11:10:11Z")

</div>

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.

```auto
{		  
 "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:

```auto
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
}

```

---

<div class="post-metadata">

**Author:** ![mavwolverine](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mavwolverine](https://discuss.elastic.co/u/mavwolverine)\
**Post date:** [February 12, 2024, 7:10pm UTC](https://discuss.elastic.co/t/sorting-of-classes-while-getting-top-10-classes-by-each-category-using-aggregation/352620/2 "2024-02-12T19:10:50Z")

</div>

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

```auto
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
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 11, 2024, 7:11pm UTC](https://discuss.elastic.co/t/sorting-of-classes-while-getting-top-10-classes-by-each-category-using-aggregation/352620/3 "2024-03-11T19:11:02Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.