Multiple Aggregate Query MongoDB

Question

I have the following situation:

• collection departments:

  { "_id" : 0, "name" : "Dep_A_1", "description" : "Department A1" }
  { "_id" : 1, "name" : "Dep_B_1", "description" : "Department B1" }
  { "_id" : 2, "name" : "Dep_C_1", "description" : "Department C1" }
  

• collection skills:

  { 
      "_id" : 0, 
      "name" : "Creativity", 
      "description" : "description", 
      "type" : "soft", 
      "categories" : [ 0, 2 ] 
  }
  

• collection roles:

  { 
      "_id" : 0,
      "name" : "Software Developer Automation and Control",
      "description" : "Automation and Control expert",
      "departments" : [ 0, 2 ],
      "skills" : [ { "_id" : 18, "weight" : 30 } ]
  }
  

I need a result like this:

{
    "_id" : 0, 
    "name" : "Software Developer Automation and Control", 
    "description" : "Automation and Control expert",
    "departments" : [ 
        { 
            "_id" : 0, 
            "name" : "Dep_A_1",
            "description" : "Department A1" 
        }, 
        { 
            "_id" : 2, 
            "name" : "Dep_C_1", 
            "description" : "Department C1" 
        }
    ], 
    "skills" : [ 
        {
            "_id" : 0, 
            "name" : "Creativity", 
            "description" : "description", 
            "type" : "soft", 
            "weight" : 30
        }
    ]
}

I need to replace the role.departments and role.skills arrays with the respective object in the departments and roles collection. Is there a way to query Mongo and get a result like this?

Anyway I am using Mongo 3.6 and Pymongo. Thanks.

Answer 1

In order to avoid the costly $unwind and $group stages you would do this instead:

db.roles.aggregate([{
    $lookup: {
        from: 'departments',
        localField: 'departments',
        foreignField: '_id',
        as: 'departments'
    }
}, {
    $lookup: {
        from: 'skills',
        let: {
            "skills": "$skills" // keep a reference to the "skills" field of the current document and make it accessible via "$$skills"
        },
        pipeline: [{
            $match: {
                $expr: {
                    $in: [ "$_id", "$$skills._id" ] // this just does the "joining"
                }
            }
        }, {
            $addFields: { // add a new field
                "weight": { // called "weight"
                    $arrayElemAt: [ // which shall be set to the correct "weight"
                        "$$skills.weight", // that can be found in the "weight" field of the "skills" array
                        { $indexOfArray: [ "$$skills._id", "$_id" ] } // at the position that has the matching "_id" value
                    ]
                }
            }
        }],
        as: 'skills'
    }
}])

Answer 2

Simply use $lookup twice:

db.getCollection('roles').aggregate([
    {$lookup: {
        from: 'departments',
        localField: 'departments',
        foreignField: '_id',
        as: 'departments'
    }},
    {$lookup: {
        from: 'skills',
        localField: 'skills._id',
        foreignField: '_id',
        as: 'skills'
    }}
])

EDIT: Now roles.skills.weight is correctly preserved:

db.getCollection('roles').aggregate([
    {$unwind: '$skills'},
    {$lookup: {
        from: 'skills',
        localField: 'skills._id',
        foreignField: '_id',
        as: 'skillsInfo'
    }},
    {$unwind: '$skillsInfo'},
    {$addFields: {skills: {$mergeObjects: ['$skills', '$skillsInfo']}}},
    {$project: {skillsInfo: 0}},
    {$group: {
        _id: '$_id',
        name: {$first: '$name'},
        description: {$first: '$description'},
        departments: {$first: '$departments'},
        skills: {$push: '$skills'}
    }},
    {$lookup: {
        from: 'departments',
        localField: 'departments',
        foreignField: '_id',
        as: 'departments'
    }}
])