1

I am quite a newbie and have a problem for mongodb aggregation. I used mongoose.

var SubjectScore = new Schema({
    name: {type:String, required:true}, //math, science, history, ...
    score: {type:Number, required:true } // 95, 85, 77,....
});

var Subject = new Schema({
    year: Number,  //2012, 2013, 2014
    subjectScore : [SubjectScore]
});

var StudentSchema = new Schema({
    name: String,
    subject: [Subject], //array length varies for each student
    profile: String,
});

So, Input data is like this.

{ _id: 54c921aaa7918d4e4a8c7e51, 
  name: John, 
  profile: "He is nice",
  subject: [{ year: 2010, 
              subjectScore: [{ name:"history" score:66},
                            { name:"math", score:65},
                            { name:"science", score:87}] }]
            },{ year: 2011, 
              subjectScore: [{ name:"history" score:75},
                            { name:"math", score:61},
                            { name:"science", score:92}] }]
            },{ year: 2012, 
              subjectScore: [{ name:"history" score:83},
                            { name:"math", score:82},
                            { name:"science", score:86}] }]
            },{ year: 2013, 
              subjectScore: [{ name:"history" score:77},
                            { name:"math", score:99},
                            { name:"science", score:71}] }]
            }]
}

The final result I want to get is like follows.

[
{ _id: "54c921aaa7918d4e4a8c7e51", 
   name: "John"
   profile: "He is nice",
   avgScore: [ 
              {name: "math", score: 77}, 
              {name:"history", score:78},
              {name:"science", score:86}  ]
   totalAvg: 82
},
{ _id: "54c921aaa7918d4e4a8c7e5b", 
   name: "Mary"
   profile: "She is kind",
   avgScore: [ 
              {name: "math", score: 67}, 
              {name:"history", score:99},
              {name:"science", score:96}  ]
   totalAvg: 82
},
{ _id: "54c921aaa7918d4e4a8c7e56", 
   name: "Jane"
   profile: "She is smart",
   avgScore: [ 
              {name: "math", score: 99}, 
              {name:"history", score:99},
              {name:"science", score:99}  ],
   totalAvg: 99
}
 ..... // 7 more student for first page result
]

I tried following but I couldn't get the field of name, profile. So I needed additional query for getting name&profile fields and sorting again.

  {$project:{subject:1}},
  {$unwind:"$subject"},
  {$unwind:"$subject.subjectScore"},
  {$group:{_id:{studentId:"$_id", subjectName:"$subject.subjectScore.name"},
           avgScore:{$avg: "$subject.subjectScore.score"}}},
  {$group:{_id:"$_id.studentId", 
           avgScore:{$push: {name:"$_id.subjectName", score:"$avgScore"}}, 
           totalAvg:{$avg:"$avgScore"}}},
  {$sort:{totalAvg:-1}},
  {$limit:10} // for first page (students per page : 10)
  1. I want know how to keep the fields which is not necessary for aggregation but need to show as result. If it's not possible, do I need additional queries for result I want?

  2. Are there another way to get this result considering performance?

I've already put a few days for this job and googling, but didn't get an answer. Help me please.

Thank you.

1 Answer 1

1

You are losing the information in your first operation because the $project is only sending along the value of the subject key.

The operation to keep the value of fields unaffected by aggregation calculations (name and profile) is with $first. It will just grab the first value of that field which should be the same as the rest of them.

{ $unwind: "$subject" },
{ $unwind: "$subject.subjectScore" },
{ $group: { _id: { sId: "$_id", subjectName: "$subject.subjectScore.name" },
            avgScore: { $avg: "$subject.subjectScore.score" },
            name: { $first: "$name" },
            profile: { $first: "$profile" } } },
{ $group: { _id: "$_id.sId",
            name: { $first: "$name" },
            profile: { $first: "$profile" },
            avgScore: { $push: { name: "$_id.subjectName", score: "$avgScore" } },
            totalAvg: { $avg: "$avgScore" } } }
{ $sort: { totalAvg: -1 } }, // this is sorting desc, but sample is asc?
{ $limit: 10 }
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.