CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
John
John

Reputation: 1092

Grouping in MongoDb using aggregate

I am a beginner to MongoDB and I found the Aggregate function hard to understand.

I read many topics and tried many things, however I couldn't get the results I am looking for.

Actually, I have two schema as:

1) Faculty.js

const FacultySchema = new Schema({

    name: {
        type: String,
        required: true
    }

});

2) Semester.js

const SemesterSchema = new Schema({

    name: {
        type: String,
        required: true
    },

    faculty: {
        type: Schema.Types.ObjectId,
        ref: 'faculties'
    }   

});

Semester collection

[
  {
    "_id": ObjectId("5bf82da745209d0d48a91b62"),
    "name": "1st Semester",
    "faculty": ObjectId("5bf7f39a1972dd0b6c74de7d"),
    "__v": 0
  },
  {
    "_id": ObjectId("5bf8c3f945209d0d48a91b63"),
    "name": "2nd Semester",
    "faculty": ObjectId("5bf7f39a1972dd0b6c74de7d"),
    "__v": 0
  },
  {
    "_id": ObjectId("5bf8c3fe45209d0d48a91b64"),
    "name": "3rd Semester",
    "faculty": ObjectId("5bf7f39a1972dd0b6c74de7d"),
    "__v": 0
  },
  {
    "_id": ObjectId("5bf8c40345209d0d48a91b65"),
    "name": "4th Semester",
    "faculty": ObjectId("5bf7f39a1972dd0b6c74de7d"),
    "__v": 0
  }
]

What I want to group is all those semesters as an array having same faculty id in one place. Something like:

[
{faculty: "BBA", semesters: ['first', 'second', 'third']},
{faculty: "BCA", semesters: ['first', 'second', 'third']}
];

How can I achieve this??

Upvotes: 1

Views: 60

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use $group aggregation to first find the distinct faculties and then $lookup to get the names of the faculties from the Faculties collection

Semester.aggregate([
  { "$group": {
    "_id": "$faculty",
    "semesters": { "$push": "$name" }
  }},
  { "$lookup": {
    "from": "faculties",
    "let": { "facultyId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$facultyId"] }}}
    ],
    "as": "faculty"
  }},
  { "$project": {
    "semesters": 1, "faculty": { "$arrayElemAt": ["$faculty.name", 0] }
  }}
])

Or you can use $lookup first and then $grouping the distinct names

Semester.aggregate([
  { "$lookup": {
    "from": "Faculty",
    "let": { "facultyId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$facultyId"] }}}
    ],
    "as": "faculty"
  }},
  { "$unwind": "$faculty" },
  { "$group": {
    "_id": "$faculty.name",
    "semesters": { "$push": "$name" }
  }},
  { "$project": {
    "semesters": 1, "faculty": "$_id", "_id": 0 }
  }}
])

Upvotes: 1

Related Questions