VishnuKumar Pv VishnuKumar Pv - 15 days ago 5
PHP Question

Mongo Db aggregate $lookup returns empty array

when trying to "JOIN" operation with $lookup but results count is ok but "as" document is empty

I have two collections and i need to get user details from subscribercol with user_id in employer_jobscol

subscribercol

{
"_id" : ObjectId("58187e7551d244640626d7e1"),
"type" : "job_seeker",
"firstname" : "vishnu",
"lastname" : "kumar pv",
"email_array" : {
"primary" : "test@test.com",
"secondary" : "test@test.test",
"verified" : false
},
"address_array" : {
"address" : "test address22d",
"streetname" : "test222d",
"pincode" : "test222d",
"city" : "dddd",
"state" : "ALASKA2d",
"country" : "Argentinad"
},
"phone_array" : {
"primary" : "",
"secondary" : "",
"verified" : ""
},
"languages" : [
"english",
"malayalam",
"english2"
]
}


employer_jobscol

{
"_id" : ObjectId("582ada6b51d244073e2a7541"),
"employer_id" : ObjectId("58187e7551d244640626d7e1"),
"job_id" : "testjob16946",
"job_title" : "Test Job 25",
"category" : "IT",
"vacancies" : "5",
"salary" : "200000",
"location" : "Kollam",
"employer_name" : "test test",
"mobile" : "9123456987",
"video" : "",
"image" : "",
"work_place" : "option1",
"email" : "test@test.test",
"skills" : [
"php"
],
"isActive" : true,
"applied_users" : [
{
"user_id" : ObjectId("581b364751d2445c311cf6f1"),
"accepted" : false
},
{
"user_id" : ObjectId("58187e7551d244640626d7e1"),
"accepted" : false
}
]
}


my database query here, (executed with Robomongo )

db.getCollection('employer_jobscol').aggregate([ {
$unwind: "$applied_users"
},
{
$lookup:
{
from: "subscribercol",
localField: "user_id",
foreignField: "_id",
as: "subscribercol_docs"
}
}
])



Result is





{
"_id" : ObjectId("582ada6b51d244073e2a7541"),
"employer_id" : ObjectId("58187e7551d244640626d7e1"),
"job_id" : "testjob16946",
"job_title" : "Test Job 25",
"category" : "IT",
"vacancies" : "5",
"salary" : "200000",
"location" : "Kollam",
"employer_name" : "test test",
"mobile" : "9123456987",
"video" : "",
"image" : "",
"work_place" : "option1",
"email" : "test@test.test",
"skills" : [
"php"
],
"isActive" : true,
"applied_users" : {
"user_id" : ObjectId("58187e7551d244640626d7e1"),
"accepted" : false
},
"subscribercol_docs" : []
}


here subscribercol_docs is empty array i need user info (name, address etc..),

enter image description here

Answer

Because there is no user_id field in local document its "applied_users.user_id" Try this

db.getCollection('employer_jobscol').aggregate([   {
      $unwind: "$applied_users"
   },
    {
      $lookup:
        {
          from: "subscribercol",
          localField: "applied_users.user_id", // <-- check here
          foreignField: "_id",
          as: "subscribercol_docs"
        }
   } 
])
Comments