CODEWITHSUNDEEP
arraysmongodbmongoosemongodb-queryaggregation-framework
HeelMega
HeelMega

Reputation: 518

Aggregate multiple collections based on student Id

I am trying to aggregate 2 collections in MongoDB based on a student's ID. One collection consists of student personal information, another one consists of the students logs. The issue is that the data is in array which is why I think my aggregation is not working. Any help will be appreciated.

student collection

{
    "_id" : ObjectId("(Object ID here"),
    "data" : [ 
        {
            "name" : "John",
            "id" : 1
        }, 
        {
            "name" : "Sandy",
            "id" : 2
        }
    ]
}

logs collection

{
    "_id" : ObjectId("(Object ID here"),
    "logs" : [ 
        {
            "studentId" : 1,
            "activity" : "11112,334,123"
        }, 
        {
            "studentId" : 2,
            "activity" : "11112,334,123"
        }
    ]
}

Here is what I have tried:

dbo.collection("student").aggregate([
    { "$lookup": {
        "localField": "data.id",
        "from": "logs",
        "foreignField": "logs.studentId",
        "as": "studentInfo"
    }
    }]).toArray(function(err, results) {
        console.log(results);
    });

Expected result:

studentinfo: {
    id: 1,
    name: "John",
    activity" : "11112,334,123"
}

Upvotes: 1

Views: 232

Answers (1)

Ashh
Ashh

Reputation: 46481

You can use below aggregation with mongodb 3.6

So basically your foreign field is an array you need to use $lookup with the pipeline to $unwind the foreign array inside the $lookup pipeline and to match the corresponding ids.

db.students.aggregate([
  { "$lookup": {
    "from": "logs",
    "let": { "dataId": "$data.id" },
    "pipeline": [
      { "$unwind": "$logs" },
      { "$match": { "$expr": { "$in": ["$logs.studentId", "$$dataId"] }}},
      { "$replaceRoot": { "newRoot": "$logs" }}
    ],
    "as": "students"
  }}
])

or use this to merge both the arrays

db.students.aggregate([
  { "$lookup": {
    "from": "logs",
    "let": { "dataId": "$data.id" },
    "pipeline": [
      { "$unwind": "$logs" },
      { "$match": { "$expr": { "$in": ["$logs.studentId", "$$dataId"] }}},
      { "$replaceRoot": { "newRoot": "$logs" }}
    ],
    "as": "students"
  }},
  { "$project": {
    "students": {
      "$map": {
        "input": "$students",
        "in": {
          "studentId": "$$this.studentId",
          "activity": "$$this.activity",
          "name": { "$arrayElemAt": ["$data.name", { "$indexOfArray": ["$data.id", "$$this.studentId"] }]}
        }
      }
    }
  }}
])

Output

[
  {
    "students": [
      {
        "activity": "11112,334,123",
        "name": "John",
        "studentId": 1
      },
      {
        "activity": "11112,334,123",
        "name": "Sandy",
        "studentId": 2
      }
    ]
  }
]

Upvotes: 2

Related Questions