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.

What have you tried so far? Can you show something?

Akrion
– Akrion

2018-08-06 17:28:53 +00:00
Commented Aug 6, 2018 at 17:28 — Akrion
– Akrion, Commented Aug 6, 2018 at 17:28

dnickless · Accepted Answer · 2018-08-06 20:27:01Z

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'
    }
}])

answered Aug 6, 2018 at 20:27

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

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

2 Comments

Massimo Lavermicocca Over a year ago

Hi, your answer produces the following error: ' $arrayElemAt's second argument must be a numeric value, but is object'

dnickless Over a year ago

Are you sure your are using the latest version of the code? I think there was a $ missing initially in front of the "indexOfArray" which I added later. It certainly works on my machine - I just tried again.

Radosław Miernik · Accepted Answer · 2018-08-06 18:47:17Z

1

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'
    }}
])

edited Aug 6, 2018 at 18:47

answered Aug 6, 2018 at 17:59

Radosław Miernik

4,0948 gold badges36 silver badges39 bronze badges

1 Comment

Massimo Lavermicocca Over a year ago

This works perfect with departments but this way I loose the value of roles.skills.weight. Any solution?

Collectives™ on Stack Overflow

Multiple Aggregate Query MongoDB

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related