There are 6 collections with references: School < Program < Subject < Lesson < Student << Mark. The task is getting a StudentA rating by subjects (the list) based on the formula: AVG Marks by "Program" compared with AVG marks of StudentA by "Subject".
Two challenges:
- how to get AVG marks by the program if we store Lesson ID only in Mark's doc! NOT Program. NOTE: Prefiltering Subjects by Program's ID and using it with $in[IDs...] will cost a huge amount of performance. Bless you, the one who knows how to make it without "related Subjects collection"
- Because the list (student | subject | score) needs to be sorted ASC by a score, and the result of the calculation will be accessible at the end of the query only, it makes pagination close to impossible. If we cut the array at the end of the hard work the server did, it will not make any sense.
With this being said, three questions:
- How does MongoDB search referred docs (does it even search, or is there some storage mapping?);
- How to execute cross docs $lookup without "garbage collection"?
- What is the best practice to paginate docs if they should be sorted by the calculated value we get during aggregation?
PS: Some of DB Ninjas claim it's super easy to implement such complicated aggregations with Neo4j. Your expert opinion would be highly appreciated. Especially with an example that answers "why?"
Thank you, friends!