The following collection contains a list table of nested structure. One computing scenario is to compute values in the list table and get records containing the largest value. Below is scores collection that stores student scores of a number of subjects. We are trying to get students obtaining the highest average score.
{
"StudentID":"S0001",
"Scores":[{"Course":"Chinese","Score":75},{"Course":"Maths","Score":81},
{"Course":"English","Score":78}]
}
{
"StudentID":"S0002",
"Scores":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":78},
{"Course":"English","Score":76}]
}
{
"StudentID":"S0001",
"Scores":[{"Course":"Chinese","Score":78},{"Course":"Maths","Score":75},
{"Course":"English","Score":72}]
}
The MongoDB way of getting the task done is like this: use $unwind under aggregate to expand each Scores value into multiple rows, group the rows by StudentID and calculate the average score avg_score, group and sort records by avg_score, get records of student IDs having the highest average score, use $unwind to expand each student ID, and finally, show the eligible records.
It is convenient to perform the computing task using esProc SPL. It will return the eligible records according to the highest average score.
1. Write script scores.dfx in esProc:
2. Start debugging and execute the code. Below is the value of cell A2:
3. Execute the script and below is the value of A3:
You can alos use esProc SPL to sum scores of subjects and get the max or min value.
Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.
Top comments (0)