What is the best way to calculate a number of values and get the sorted result on the basis of calculated values?

Suppose i have a collection with some data, with a user activities and points for the each activity. I want to calculate the sum of all the activity points for each number and return then result as height to lowest points. What is the best way to do that. Doing sorting is not good because data with the calculated scored will be unindexed, and maxN operator is also not available as i am using mongo 5.0. so how can I solve this problem in an efficient way?

Hello @sajan_kumar ,

Please correct me if my understanding of your use case is wrong, you want to do a sum of values based on specific groupings, and then sort the results from high to low (descending order)?

To understand your use case better could you provide below details:

  • some example documents
  • output expected

You are right on this as in memory sorting can not use indexes. Since an index entry points to a specific physical document, you cannot use an index to sort something that has no physical presence. So if an index use is desired then it is required to actually create a physical document for the computed field, which the index can now point to. MongoDB provides a Materialized View feature, which is typically the result of a $merge or $out stage. You can create indexes directly on on-demand materialized views because they are stored on disk. It could provide a performance boost at the cost of extra storage space, and you will also need a way to trigger the data refresh so that the view remains up to date.