Conditional Sum of Array Elements on Single Object in Mongo

Question

This is an optional review problem from a study guide, not worth credit. I'm trying to find the number of computer science courses taken in the spring and fall combined for each student id with their major. An example document with only the required fields for this query:

> db.students.find().limit(1).pretty();
{
    "_id" : 10456673,
    "major" : "CSC",
    "fall" : {
        "units" : 12,
        "courses" : [
            "CSC 365",
            "CSC 419",
            "MATH 428"
        ]
    },
    "spring" : {
        "units" : 16,
        "courses" : [
            "CSC 430",
            "CSC 453",
            "BIO 161",
            "HIST 217"
        ]
    }
}

What I believe to be the best partial query I've written so far:

db.students.aggregate(
   {
      $unwind: "$fall.courses"
   },
   {
      $match: {
         "fall.courses": {
            $in: ["CSC 103", "CSC 225", "CSC 349", "CSC 357", "CSC 365", "CSC 419", "CSC 430", "CSC 453"]
         }
      }
   },
   {
      $group: {
         "_id": {
            "id": "$_id",
            "spring": "$spring.courses",
            "major": "$major"
         },
         "fallCsc": {
            $sum: 1
         }
      }
   },
   {
      $unwind: "$_id.spring"
   }
);

This outputs:

{ "_id" : { "id" : 10048392, "spring" : "EE 461", "major" : "EE" }, "fallCsc" : 1 }
{ "_id" : { "id" : 10048392, "spring" : "CSC 315", "major" : "EE" }, "fallCsc" : 1 }
{ "_id" : { "id" : 10048392, "spring" : "CHM 231", "major" : "EE" }, "fallCsc" : 1 }
{ "_id" : { "id" : 72039451, "spring" : "CSC 357", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 72039451, "spring" : "CSC 349", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 72039451, "spring" : "STAT 312", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 72039451, "spring" : "KIN 223", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 10456673, "spring" : "CSC 430", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 10456673, "spring" : "CSC 453", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 10456673, "spring" : "BIO 161", "major" : "CSC" }, "fallCsc" : 2 }
{ "_id" : { "id" : 10456673, "spring" : "HIST 217", "major" : "CSC" }, "fallCsc" : 2 }

However, I don't understand how to now count and add the number of CSC spring classes to the fallCsc value, without losing the fall value for students who haven't taken CSC classes in the spring, but have in the fall.

This problem occurs when I try to $match CSC classes only for the spring field above and then $group by student id, $summing the spring CSC classes for the grouped students. If the $match removes all classes taken by the student id, they are not in the grouped results at all.

How do I avoid losing these students, or is this the wrong approach?

Edit: Also, it seems like I need to conditionally $sum as I $group by only the student id, storing the field as springCsc and $project the final value by combining with the fallCsc value, but I can't get $sum to work with $cond.

Puneet Singh · Accepted Answer · 2017-02-20 06:03:29Z

1

If you just want id, major and total no of CS courses taken combined in spring and fall, below query will work. But if you want any more thing, then need to write a different query.

db.getCollection('test').aggregate([
   { $project: { major:"$major", allcourses: { $concatArrays: [ "$fall.courses", "$spring.courses" ] } } },
   {$unwind: "$allcourses"},
   {
      $match: {
         "allcourses": {
            $in: ["CSC 103", "CSC 225", "CSC 349", "CSC 357", "CSC 365", "CSC 419", "CSC 430", "CSC 453"]
         }
      }
   },
   {
      $group: {
         "_id": {
            "id": "$_id",
            "major": "$major"
         },
         "total_cs_course": {
            $sum: 1
         }
      }
   }
]);

answered Feb 20, 2017 at 6:03

Puneet Singh

3,5731 gold badge28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Goodrich Over a year ago

I couldn't find the $concatArrays operator for some reason! Thank you for your help. I verified the query works as expected on my dataset.

Collectives™ on Stack Overflow

Conditional Sum of Array Elements on Single Object in Mongo

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related