-
Type:
Question
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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.