CODEWITHSUNDEEP
mongodb
RonPringadi
RonPringadi

Reputation: 1494

How to Chain Left Join in MongoDB?

I'm trying to perform a chained left join in MongoDB. I can use $lookup to join from users collection to employee collection. But if I want to join one more time with the title it will fail, in another word the the 2nd .aggregate([{ $lookup }]) fails. The objective of this exercise is to display that John is using title prefix Mr. and Jane is using title prefix Mrs. while using an intermediary collection called employee. So how do I do that, or what did I do wrong?

db.users.drop();
db.users.insert({name:"John"});
db.users.insert({name:"Jane"});

db.title.drop();
db.title.insert({prefix:"Mr."});
db.title.insert({prefix:"Mrs."});

db.getCollection('users').find({});
db.getCollection('title').find({});

db.employee.drop();
db.employee.insert({userid:ObjectId("5d6522449b9bc50473af4b4f"), titleid:ObjectId("5d6522449b9bc50473af4b51")});
db.employee.insert({userid:ObjectId("5d6522449b9bc50473af4b50"), titleid:ObjectId("5d6522449b9bc50473af4b52")});
db.getCollection('employee').find({});

db.users.aggregate
([{
   $lookup:
     {
       from: 'employee',
       localField: '_id',
       foreignField: 'userid',
       as: 'R'
     }
   },
]).aggregate
([{
   $lookup:
     {
       from: 'title',
       localField: '_id',
       foreignField: R[0].titleid,
       as: 'T'
     }
   },
]);

Upvotes: 0

Views: 229

Answers (2)

varshneyanmol
varshneyanmol

Reputation: 247

What you are doing wrong here is appending another query to the result of first query which will not work.

In my opinion you should write aggregate on employee collection rather than users collection which will look cleaner.

Anyway, I've written the correct query based on your question, and have aggregated on users collection

try this:

db.users.aggregate([
{$lookup: {
    from: "employee",
    localField: "_id",
    foreignField: "userid",
    as: "R"
}},
{$unwind: "$R"},
{$lookup: {
    from: "title",
    localField: "R.titleid",
    foreignField: "_id",
    as: "T"
}},
{$unwind: "$T"},
{$project: {name: 1, title: "$T.prefix", _id: 0} }
])

This will output documents like:

{ "name" : "John", "title" : "Mr." }
{ "name" : "Jane", "title" : "Mrs." }

The last $project stage is optional. You can omit it but then output will be pretty messy.

Upvotes: 3

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

The following query can get us the expected output:

db.users.aggregate([
    {
        $lookup:{
            "from":"employee",
            "localField":"_id",
            "foreignField":"userid",
            "as":"employeeLookup"
        }
    },
    {
        $unwind:"$employeeLookup"
    },
    {
        $lookup:{
            "from":"title",
            "localField":"employeeLookup.titleid",
            "foreignField":"_id",
            "as":"titleLookup"
        }
    },
    {
        $unwind:"$titleLookup"
    },
    {
        $project:{
            "_id":0,
            "name":{
                $concat:[
                    "$titleLookup.prefix",
                    " ",
                    "$name"
                ]
            }
        }
    }
]).pretty()

Data set:

users:

{ "_id" : ObjectId("5d6525f922a848de552bdcf6"), "name" : "John" }
{ "_id" : ObjectId("5d6525fa22a848de552bdcf7"), "name" : "Jane" }

title:

{ "_id" : ObjectId("5d6525fa22a848de552bdcf8"), "prefix" : "Mr." }
{ "_id" : ObjectId("5d6525fb22a848de552bdcf9"), "prefix" : "Mrs." }

employee:

{
    "_id" : ObjectId("5d65265422a848de552bdcfa"),
    "userid" : ObjectId("5d6525f922a848de552bdcf6"),
    "titleid" : ObjectId("5d6525fa22a848de552bdcf8")
}
{
    "_id" : ObjectId("5d65265422a848de552bdcfb"),
    "userid" : ObjectId("5d6525fa22a848de552bdcf7"),
    "titleid" : ObjectId("5d6525fb22a848de552bdcf9")
}

Output:

{ "name" : "Mr. John" }
{ "name" : "Mrs. Jane" }

Upvotes: 0

Related Questions