How to find non aggregated field when using group by in aggregation

Working with Data
1

I want to find name of student with highest mark using group by student.

db.student.aggregate([{ $group: { _id: "$subject", marks: { $min: "$marks" } } }]);
using project in aggregation wont work

Data :

[
  {
    _id: ObjectId('669b5ec1ded042cdc2482f92'),
    Rollno: 2,
    name: 'anusha',
    subject: 'OSD',
    marks: 75
  },
  {
    _id: ObjectId('669b5ec1ded042cdc2482f93'),
    Rollno: 3,
    name: 'ravi',
    subject: 'TOC',
    marks: 69
  },
  {
    _id: ObjectId('669b5ec1ded042cdc2482f94'),
    Rollno: 4,
    name: 'veena',
    subject: 'TOC',
    marks: 70
  },
  {
    _id: ObjectId('669b5ec1ded042cdc2482f95'),
    Rollno: 5,
    name: 'Pravini',
    subject: 'OSD',
    marks: 80
  },
  {
    _id: ObjectId('669b5ec2ded042cdc2482f96'),
    Rollno: 6,
    name: 'Reena',
    subject: 'DMSA',
    marks: 50
  },
  {
    _id: ObjectId('669b5ec2ded042cdc2482f97'),
    Rollno: 7,
    name: 'Geeta',
    subject: 'CN',
    marks: 90
  },
  {
    _id: ObjectId('669b5eeaded042cdc2482f98'),
    Rollno: 1,
    name: 'Navin ',
    subject: 'DMSA',
    marks: 78
  }
]
2

Hello @Khushi_Agarwal2, Welcome,

You can group by multiple fields,

_id: { name: "$name", subject: "$subject" }

Read more about $group stage, it’s easy to understand from the documentation,

3

Thanks Vishal for helping with the problem
Sorry I forgot to mention that I need highest marks of student fir each subject
so i tried to do it and this query is giving the correct result and i was trying to optimise it

db.student.aggregate([ { $group: { _id: “$subject”, marks: { $max: “$marks” } } },
{ $lookup: { from: “student”, localField: “_id”, foreignField: “subject”, let: { highestMarks: “$marks” }, pipeline: [ { $match: { $expr: { $eq: [“$$highestMarks”, “$marks”] } } },
{ $project: { name: 1, _id: 0 } }], as: “matches” } },
{ $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: [“$matches”, 0] }, “$$ROOT”] } } },
{ $project: { matches: 0 } }])

4

Hello @Khushi_Agarwal2,

What if multiple people have similar maximum marks for a subject, which student name should come?

5

multiple names should be displayed with rollno then for that particular subject.

6

I am still not getting your expected result, in which format should it displayed? Please provide sample documents and the expected outcome.

Look at this query (not including the case of this similar mark)

7

I think so this query is giving the desired result

1 Like
8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.