MongoDb Need help in finding highest value in database using aggregate

Question

I am working on a query to find the subject title, subject type, and credit value of subject with highest credit value. This is the query I have come up with:

 db.Subject.aggregate([{$match:{"subject.credit":{$max:true}},
{$project:{"subject.subTitle":1, "subject.type":1, "subject.credit":1,  _id: 0}}
]).pretty()

This doesn't seem to net anything. and

db.Subject.aggregate([{$match:{"subject.credit":$max}},
$project:{"subject.subTitle":1, "subject.type":1, "subject.credit":1,  _id: 0}}
]).pretty()

This nets and error as shown below:

E QUERY    [js] ReferenceError: $max is not defined :
@(shell):1:32

Here is a part of the database that contains the highest credit value:

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" }
            ]
  }
}
)

Minsky · Accepted Answer · 2020-11-18T03:55:32.837

1

A good strategy, if you don't have an index, is to collapse all documents and get one value:

const pipeline =  [
    {
        "$project" : {
            "subTitle" : "$subject.subTitle",
            "type" : "$subject.type",
            "credit" : "$subject.credit",
            "_id" : 0
        }
    },
    {
        "$sort" : {
            "credit" : -1
        }
    },
    {
        "$limit" : 1
    }
]
db.Subject.aggregate(pipeline)

Query

$project We want only a subset of fields
$sort to get the highest credit first.
$limit:1 to get only the highest.

edited Nov 18 '20 at 03:55

answered Nov 17 '20 at 09:46

Minsky

2,277
10
19

was it really needed $group keyword? – dılo sürücü Nov 17 '20 at 09:49
1

what's your simpler alternative? @dılosürücü – Minsky Nov 17 '20 at 09:51
no wrong for me, just ı asked ...$group keyword does by group ,no problem – dılo sürücü Nov 17 '20 at 09:53
$group is necessary to find the max in _all_ documents, otherwise you find the max in an array from _one_ document – Minsky Nov 17 '20 at 09:54
Your query does help me find the credit score with the highest value but I am required to list out all subject title, type and credit for the subjects with the highest credit value instead of just the highest credit value. If its not too much trouble for you, it is possible to craft another query? – Frio_Penitencia Nov 18 '20 at 02:53
1

does it work as you expect? @Frio_Penitencia – Minsky Nov 18 '20 at 03:48
1

I was able to figure earlier myself, but your answer is way better. Thx for being so responsive! – Frio_Penitencia Nov 18 '20 at 04:05

score 0 · Answer 2 · answered Nov 17 '20 at 09:42

0

db.Subject.find({},{"subject.subTitle":1, "subject.type":1, "subject.credit":1,"_id":0}).sort({"subject.credit":-1})

just use this

answered Nov 17 '20 at 09:42

vicky

415
2
10

I am only looking to list out the subject title, type and credit for the subjects with the highest credit value. After testing your query, it lists out the subjects with the highest credit value 1st but also lists out every other entry. – Frio_Penitencia Nov 18 '20 at 02:33
I am also only allowed to utilize aggregate. – Frio_Penitencia Nov 18 '20 at 02:39

MongoDb Need help in finding highest value in database using aggregate

2 Answers2