RaqForum 28 No.
368 View  • 
     ...

How to Compute Arrays and Get Record Containing the Largest Value on a MongoDB Collection

 
array(2) aggregate(2) geting max/min(1)

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:

A

B

1

=mongo_open("mongodb://localhost:27017/raqdb")

/ Connect to MongoDB database

2

=mongo_shell(A1,"scores.find(,   {_id:0})").fetch()

/ Get data from scores collection

3

=A2.maxp@a(Scores.avg(Score))

/ Return records according to the highest average score

4

>A1.close()

/ Close database connection

  2. Start debugging and execute the code. Below is the value of cell A2:

A2

StudentID

Scores

S0001

[[Chinese ,75.0],[  Maths,   81.0],[English, 78.0]]

S0002

[[Chinese ,80.0],[    Maths, 78.0],[English, 76.0]]

S0003

[[Chinese ,78.0],[    Maths, 75.0],[English, 72.0]]

    3. Execute the script and below is the value of A3:

A3

StudentID

Scores

S0001

[[Chinese ,75.0],[  Maths,   81.0],[English, 78.0]]

S0002

[[Chinese ,80.0],[    Maths, 78.0],[English, 76.0]]

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.


array(2) aggregate(2) geting max/min(1)
 
 •  368 View  •