CODEWITHSUNDEEP
mongodbmongodb-querynosqlaggregation-framework
Akki
Akki

Reputation: 123

How to add Multiple Group By with different conditions output in single query in mongodb?

Hello I am new to mongodb from sql, I want a particular data in mongodb which can be easily available in sql using subquery Please help how to get the required output in mongodb .

Need to get average of students in different subjects in single object with student name

Stored Object :

[
{StudentsName:"AKshay",_id:"feferf","subject_name":"SubjectA","marks":"28"},
 { StudentsName:"AKshay",_id:"feferf","subject_name":"SubjectB","marks":"50"} , 
{StudentsName:"AKshay",_id:"feferf","subject_name":"SubjectA","marks":"28"},
  {StudentsName:"AKshay",_id:"feferf","subject_name":"SubjectB","marks":"50"},
]

Required output:

{StudentsName:"AKshay",_id:"feferf","SubjectA":"28","SubjectB":"50"}

Have used group by using match but it gives different array object in different objects with same _id . Even tried facet...

Upvotes: 0

Views: 112

Answers (1)

pcha
pcha

Reputation: 78

First of all, I see a design smell here, why do you have a different document for each note? I guess that you're thinking in transactional DBs but using a Document DB, where you could have different levels and add all the notes to a single document.

Said that, you can make this query with aggregation pipelines, here yo have how:

[{$group: { // First you need to group your data, I think that you could save the data already in that format and use only one step in the aggregation pipeline.
 _id: '$StuddentName',
 notesA: {
  $push: {
   $toInt: '$SubjectA' //Why your notes are strings? you could avoid the $toInt using integers directly
  }
 },
 notesB: {
  $push: {
   $toInt: '$SubjectB'
  }
 }
}}, {$project: { // Here is where you get the AVG
 avgA: {
  $avg: '$notesA'
 },
 avgB: {
  $avg: '$notesB'
 }
}}]

Or, even better, you can use the solution suggested by @takis which use only a step: https://cmql.org/play/?q=70314926

You can find the complete aggregation operators in the official mongo documentation and if you are using mongo compass or Atlas the aggregations tabs is pretty useful to try your pipelines.

Upvotes: 1

Related Questions