[SERVER-30756] How to join collection where foreign field is an array? Created: 21/Aug/17  Updated: 31/Aug/17  Resolved: 30/Aug/17

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Irshad Ansari Assignee: Mark Agarunov
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 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.



 Comments   
Comment by Irshad Ansari [ 31/Aug/17 ]

Hello Mark,

sorry, i was not aware of this, i'll take care of this in future.

Thanks
Irshad

Comment by Mark Agarunov [ 30/Aug/17 ]

Hello irshadahmed.ansari@gmail.com,

Thanks for your report. Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group.

Thanks,
Mark

Generated at Thu Feb 08 04:24:54 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.