I am storing data in Elasticsearch in a parent/child relationship in the below format:

Parent Record – Product

{
   product_id: 123,
   product_name: "Hello",
   category_id: 456,
   type: "XYZ"
}

Child Record – Reviews

{
  "product_id": "123",
  "ratings": 5,
  "year": 2024 
}

I want to get all the products for a particular category_id and the product should be sorted by the sum of all the reviews for a particular year in descending order.