How do i find the total number of subjectsthat has no prerequisites using agregation?

user_14661466 0
Viewed 62

I have tried several codes but it didn't work. Example from the database, one has a prerequisite and one does not have prerequisites and I would like to find the total number of the subject with no prerequisites :

db.Subject.insert(
{ 
    "_id":ObjectId(),
    "subject":{ 
        "subCode":"CSCI321",
        "subTitle":"Final Year Project",
        "credit":6,
        "type":"Core",
        "assessments": [
                { "assessNum": 1,
                  "weight":30,
                  "assessType":"Presentation",
                  "description":"Prototype demonstration" },
                { "assignNum": 2,
                  "weight":70,
                  "assessType":"Implementation and Presentation",
                  "description":"Final product Presentation and assessment of product implementation by panel of project supervisors" }
            ]
  }
}
)
db.Subject.insert(
{ 
    "_id":ObjectId(),
    "subject":{ 
        "subCode":"CSCI203",
        "subTitle":"Algorithm and Data Structures",
        "credit":3,
        "type":"Core",
        "prerequisite": ["csci103"]
}})

one of the few codes that I tried using :

db.Subject.aggregate({$group:{"prerequisite":{"$exists": null}, count:{$sum:1}}});

Results :

_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
@(shell):1:1
aggregation-framework mongodb-query mongodb aggregate
2 Answers

You can use $match to eliminate unwanted documents and $group to calculate sum

db.collection.aggregate([
  {
    $match: {
      "subject.prerequisite": {
        "$exists": false
      }
    }
  },
  {
    $group: {
      _id: null,
      total: {
        $sum: 1
      }
    }
  }
])

Working Mongo playground

user_79757710

This can be achieved within a single aggregation pipeline stage i.e. the $group step where you can use the BSON Types comparison order to aggregate the documents where the 'subjects.prerequisites' field exists and has at least an element. The condition can be used as the group by key i.e. the _id field in $group.

Consider running the following aggregation pipeline to get the desired results:

db.Subject.aggregate([
    { $group: {
        _id: {
            $cond: [
                { 
                    $or: [
                        { $lte: ['$subject.prerequisite', null] },
                        { 
                           $eq: [
                                { $size: { $ifNull: ['$subject.prerequisite', [] ] } },
                                0
                            ]
                        }
                    ]
                },
                'noPrerequisite',
                'havePrerequisite'
            ]
        }, 
        count: { $sum: 1 }
    } }
])

The first condition in the OR simply returns true if a document does not have the embedded prerequisites field and the other satisfies these set of conditions:

if length of ( prerequisites || [] ) is zero

In the above, $cond takes a logical condition as its first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). When used as an expression in the _id field for $group, it groups all the documents into either true/false which is conditionally projected as "noPrerequisite" (true) OR "havePrerequisite" (false) in the group key.

The results will contain both counts for documents where the prerequisite field exists and for those without the field OR it has an empty array.

user_1220050
Related