CODEWITHSUNDEEP
mongodb
Frio_Penitencia
Frio_Penitencia

Reputation: 103

MongoDb Need help in counting on the same row using aggregate

I am tasked to writing a query that requires me to list the name of subject type and total no of subjects which belongs to each subject type. Within the database, there are 2 different subject types being core and electives.

Here a 2 sample data within the database that contains the 2 different subject types:

db.Subject.insert(
{ 
    "_id":ObjectId(),
    "subject":{ 
        "subCode":"CSCI203",
        "subTitle":"Algorithm and Data Structures",
        "credit":3,
        "type":"Core",
        "prerequisite": ["csci103"],
        "assessments": [
                { "assessNum": 1,
                  "weight":10,
                  "assessType":"Assignment",
                  "description":"Assignment 1 - Mathematic Concepts and Algorithm Complexity" },
                { "assignNum": 2,
                  "weight":15,
                  "assessType":"Assignment",
                  "description":"Assignment 2 - Linear and Non-linear Data Structure" },
                { "assessNum": 3,
                  "weight":15,
                  "assessType":"Assignment",
                  "description":"Assignment 3 - Greedy Algorithm and Branch-and-Bound" },
                { "assessNum": 4,
                  "weight": 60,
                  "assessType":"Examination",
                  "description":"Closed-book Final Examination" }
            ],
        "book": [
                { "ISBN":"13:978-0-13-231681-1",
                  "bookType":"textbook",
                  "bookTitle":"Introduction to the Design and Analysis of Algorithms",
                  "edition":3,
                  "yearPub":2012,
                  "publisher":"Pearson",
                  "author": [ "Levitin" ] },
                { "ISBN":"978-0-262-53305-8",
                  "bookType":"reference",
                    "bookTitle":"Introduction to Algorithms",
                  "edition":3,
                  "yearPub":2013,
                  "publisher":"The MIT Press",
                  "author": [ "Thomas H Cormen", "Charles E Leiserson", "Ronald L Riverst", "Clifford Stein" ] } ]
  }
}
)

db.Subject.insert(
{ 
    "_id":ObjectId(),
    "subject":{ 
        "subCode":"IACT201",
        "subTitle":"Professional Practice and Ethics",
        "credit":3,
        "type":"Elective",
        "assessments": [
                { "assessNum": 1,
                  "weight":35,
                  "assessType":"Assignment",
                  "description":"Assignment 1 - Ethical Dilemma Case Study" },
                { "assignNum": 2,
                  "weight":25,
                  "assessType":"Presentation",
                  "description":"Presentation of Case Study" },
                { "assessNum": 3,
                  "weight": 40,
                  "assessType":"Examination",
                  "description":"Closed-book Final Examination" }
            ]
  }
}
)

How can I craft a query using aggregate that allows me to count the amount of core and elective subjects? I am pretty lost at the moment as both belong to subject type.

Here is an example of the desired result: {"Core Subjects": 6 "Elective Subjects": 1}

Upvotes: 0

Views: 37

Answers (2)

user12582392
user12582392

Reputation:

If there are just 2 types, this can be expressed in one line:

db.collection.aggregate([{$sortByCount: "$subject.type"}])

The only trouble is you can't make this case insensitive, as far as I know.

Upvotes: 1

varman
varman

Reputation: 8894

You can use group

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          "subject.type": "Core"
        },
        {
          "subject.type": "Elective"
        }
      ]
    }
  },
  {
    $group: {
      _id: "$subject.type",
      count: {
        $sum: 1
      }
    }
  }
])

Working Mongo playground

Upvotes: 1

Related Questions