CODEWITHSUNDEEP
mongodb
mahmoudajawad
mahmoudajawad

Reputation: 105

$lookup within the same collection is nesting docs instead of returning all docs

Dealing with $lookup was fun until I thought of makeing a join withing the same collection.

Say I have the next collection:

{'_id': ObjectId('5a1a62026462db0032897179'),
'department': ObjectId('5a1982646462db032d58c3f9'),
'name': 'Standards and Quality Department',
'type': 'sub'}, {
'_id': ObjectId('5a1982646462db032d58c3f9'),
'department': false,
'desc': 'Operations Department',
'type': 'main'}

As clearly it says, there's backlinking within the same collection using the department key which could be false to indicate highest level department.

I'm using the next query (Python) to populate the results:

query = [{'$lookup': {'as': '__department',
              'foreignField': '_id',
              'from': 'departments',
              'localField': 'department'}},
 {'$unwind': '$__department'},
 {'$group': {'__department': {'$first': '$__department'},
             '_id': '$_id',
             'department': {'$first': '$department'},
             'name': {'$first': '$name'},
             'type': {'$first': '$type'}}}]
for doc in conn.db.departments.aggregate(query): pprint(doc)

What I'm expecting to get:

{'__department': None,
 '_id': ObjectId('5a1982646462db032d58c3f9'),
 'department': false,
 'name': 'Operations Department',
 'type': 'main'},
{'__department': {'_id': ObjectId('5a1982646462db032d58c3f9'),
                  'department': 'false',
                  'name': 'Operations Department',
                  'type': 'main'},
 '_id': ObjectId('5a1a62026462db0032897179'),
 'department': ObjectId('5a1982646462db032d58c3f9'),
 'name': 'Standards and Quality Department',
 'type': 'sub'}

What I'm actually getting is:

{'__department': {'_id': ObjectId('5a1982646462db032d58c3f9'),
                  'department': 'false',
                  'name': 'Operations Department',
                  'type': 'main'},
 '_id': ObjectId('5a1a62026462db0032897179'),
 'department': ObjectId('5a1982646462db032d58c3f9'),
 'name': 'Standards and Quality Department',
 'type': 'sub'}

I'm not sure why $unwind is grouping both the docs together although before applying $unwind I do get both of them separatly.

Any suggestions?

Upvotes: 1

Views: 199

Answers (1)

Alex P.
Alex P.

Reputation: 3171

That is because you create an empty array __department in the document that didn't find a match in the $lookup. This is how your orphan document looks like:

{
    "_id" : ObjectId("5a1982646462db032d58c3f9"),
    "department" : false,
    "desc" : "Operations Department",
    "type" : "main",
    "__department" : []
}

When you are unwinding there is nothing to $unwind in this document, so it gets lost in the process. If you want to keep it you have to "normalize" your array. So you'd have to add this after your $lookup and before your $unwind:

{
        $project: {
            _id: 1,
            department: 1,
            name: 1,
            type: 1,
            __department: {
                $cond: [{
                        $eq: ["$__department", []]
                    },
                    [{
                        _id: 0,
                        department: "None", 
                        desc: "None",
                        type: "None"
                    }], '$__department'
                ]
            }
        }
    }

So all together it should look like that:

[{
        '$lookup': {
            'as': '__department',
            'foreignField': '_id',
            'from': 'depart',
            'localField': 'department'
        }
    },
    {
        '$project': {
            _id: 1,
            department: 1,
            name: 1,
            type: 1,
            __department: {
                $cond: [{
                        $eq: ["$__department", []]
                    },
                    [{
                        _id: 0,
                        department: "None", 
                        desc: "None",
                        type: "None"
                    }], '$__department'
                ]
            }
        }
    },
    {'$unwind': "$__department"},
    {'$group': {'__department': {'$first': '$__department'},
             '_id': '$_id',
             'department': {'$first': '$department'},
             'name': {'$first': '$name'},
             'type': {'$first': '$type'}}}]

Upvotes: 2

Related Questions