Details
-
Question
-
Resolution: Done
-
Major - P3
-
None
-
None
-
None
-
None
Description
I am working with mongodb and need help to join collections where the foreign field is a value in an array.
Collection 1:
(Properties)
-----------
> db.properties.findOne({'agentid':'mike@gmail.com'},{'agentid':1,'contact':1})
|
{
|
"_id" : ObjectId("56d2cc296f07d417008b4567"),
|
"agentid" : "mike@gmail.com",
|
"contact" : [
|
{
|
"type" : "listingAgent",
|
"id" : "1",
|
"name" : "Mike",
|
"telephoneType" : "",
|
"telephone" : "",
|
"email" : "mike@gmail.com"
|
}
|
]
|
}
|
collection 2:
(agents)
---------
> db.agents.findOne({'email':'mike@gmail.com'})
|
{
|
"_id" : ObjectId("5979d1400045fecc0800004c"),
|
"id" : "35",
|
"agentID" : "mike@gmail.com",
|
"email" : "mike@gmail.com",
|
"name" : "Mike",
|
"telephone" : "",
|
"status" : true
|
}
|
collection 3:
(users)
------------
> db.users.findOne({'email':'mike@gmail.com'})
|
{
|
"_id" : ObjectId("5979d0e20045feac0d00003f"),
|
"id" : "20",
|
"email" : "mike@gmail.com",
|
"type" : "publisher",
|
"status" : "inactive",
|
|
|
}
|
Now as per above collections I need to aggregate users and need to join 'users' collection with 'agents' and 'properties'.
The issue is that the collection 'properties' is having foreign field 'email' as a value in an array so i am not able to apply lookup on it.
I need to join these collections with field 'email' but due to having foreign field 'email' as a value in an array in 'properties' collections I am not getting results.
i am hereby showing my attempt as below
$query =
|
array(
|
array('$lookup'=>array(
|
'from'=>'agents`',
|
'localField'=>'email',
|
'foreignField'=>'email',
|
'as'=>'users_agents'
|
)
|
),
|
|
|
array('$unwind'=>'$properties.contact'),
|
|
array('$lookup'=>array(
|
'from'=>'properties',
|
'localField'=>'email',
|
'foreignField'=>'contact.email',
|
'as'=>'users_properties'
|
)
|
),
|
|
|
|
array('$match'=>array(
|
'$and'=>array(array('users_properties.offline'=>false),
|
array('users_agents.status'=>true)))),
|
|
|
|
|
array('$project'=>array(
|
'_id'=>1,
|
'id'=>1,
|
'username'=>1,
|
'firstname'=>1,
|
'lastname'=>1,
|
'email'=>1,
|
'type'=>1,
|
'status'=>1,
|
)
|
),
|
|
|
);
|
|
|
$collection_users->aggregate ($query );
|
Can anyone help me out please.