Btukfyl

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"

}

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

      }

    ]

  }

]