MongoDB Aggregation for every element of array field of a document

Question

I have 2 collections -

Student collection (sample student document)

{
'id': '123',
'name': 'john',
'age': 25,
'fav_colors': ['red', 'black'],
'marks_in_subjects': [
    {
     'marks': 90,
     'subject_id': 'abc'
    },
    {
     'marks': 92,
     'subject_id': 'def'
    }
 ]
}

Subjects collection (2 sample documents)

{
'id': 'abc',
'name': 'math'
},
{
'id': 'def',
'name': 'physics'
}

When I query for student document for id: '123', I want the resulting output as:

{
'id': '123',
'name': 'john',
'age': 25,
'fav_colors': ['red', 'black'],
'marks_in_subjects': [
    {
     'marks': 90,
     'subject_id': 'abc',
     'subject_name': 'math'
    },
    {
     'marks': 92,
     'subject_id': 'def',
     'subject_name': 'physics'
    }
 ]
}

Now, I read the MongoDB aggregation pipelines and operators document, still, I am clueless as to how to achieve this. The doubt persists because I am not even sure if this is possible with the help of mongo aggregation pipelines since JOIN happens here for every element of the array field in the student document.

It would be really helpful if anyone can help here. Thanks

turivishal · Accepted Answer · 2021-04-29 07:03:18Z

4

$match you conditions
$unwind deconstruct marks_in_subjects array
$lookup with subjects collection
$addFields to get first element name from return subject
$group by id and reconstruct marks_in_subjects array and also add your required field of root document using $first operator

db.students.aggregate([
  { $match: { id: "123" } },
  { $unwind: "$marks_in_subjects" },
  {
    $lookup: {
      from: "subjects",
      localField: "marks_in_subjects.subject_id",
      foreignField: "id",
      as: "marks_in_subjects.subject_name"
    }
  },
  {
    $addFields: {
      "marks_in_subjects.subject_name": {
        $arrayElemAt: ["$marks_in_subjects.subject_name.name", 0]
      }
    }
  },
  {
    $group: {
      _id: "$id",
      name: { $first: "$name" },
      age: { $first: "$age" },
      fav_colors: { $first: "$fav_colors" },
      marks_in_subjects: { $push: "$marks_in_subjects" }
    }
  }
])

Playground

Second option without $unwind stage,

$match you conditions
$lookup with subjects collection
$addFields to get subject name from subjects
- $map to iterate loop of marks_in_subjects array
- $reduce to iterate loop of subjects array and check condition if subject_id match then return subject name
- $mergeObjects to merge current object of marks_in_subjects and new field subject_name
$unset to remove subjects array because its not needed now

db.students.aggregate([
  { $match: { id: "123" } },
  {
    $lookup: {
      from: "subjects",
      localField: "marks_in_subjects.subject_id",
      foreignField: "id",
      as: "subjects"
    }
  },
  {
    $addFields: {
      marks_in_subjects: {
        $map: {
          input: "$marks_in_subjects",
          as: "m",
          in: {
            $mergeObjects: [
              "$$m",
              {
                subject_name: {
                  $reduce: {
                    input: "$subjects",
                    initialValue: "",
                    in: {
                      $cond: [{ $eq: ["$$this.id", "$$m.subject_id"]}, "$$this.name", "$$value"]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { $unset: "subjects" }
])

Playground

edited Apr 29, 2021 at 7:03

answered Apr 28, 2021 at 7:59

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Akshat Kumar Gupta Over a year ago

Hey @turivishal- Thanks buddy. It worked. Thanks a lot. However, I have one more doubt. Suppose there is name field also in the students collection, how can I get name field in the output document as well? I know there is one solution to push name field in the group stage of pipeline. However that sets name field as an array. How can I add name field in the output as the key value field?

turivishal Over a year ago

you just need only name field or other fields required?

Akshat Kumar Gupta Over a year ago

I mean I want every other field in the original document to remain in the final resulting document. So if the original students document has n other fields, I want them to persist in the final resulting document after adding subjects name in the marks in subject field. I hope I was clear. Thanks again for giving time to this.

turivishal Over a year ago

can you just show your expected result, just change result from playground and share here.

Akshat Kumar Gupta Over a year ago

@turivishal- I have updated the input and output in the question details :)

|

Tushar Gupta - curioustushar · Accepted Answer · 2021-04-28 07:59:51Z

3

Demo - https://mongoplayground.net/p/H5fHpfWz5VH

db.Students.aggregate([
  {
    $unwind: "$marks_in_subjects" //  break into individual documents
  },
  {
    "$lookup": { // get subject details
      "from": "Subjects",
      "localField": "marks_in_subjects.subject_id",
      "foreignField": "id",
      "as": "subjects"
    }
  },
  {
    $set: { // set name
      "marks_in_subjects.name": "subjects.0.name" // pick value from 0 index
    }
  },
  {
    $group: { // join document back by id
      _id: "$_id",
      marks_in_subjects: { $push: "$marks_in_subjects" }
    }
  }
])

answered Apr 28, 2021 at 7:59

Tushar Gupta - curioustushar

57.2k24 gold badges106 silver badges110 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregation for every element of array field of a document

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related