Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-30756

How to join collection where foreign field is an array?

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 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.

      Attachments

        Activity

          People

            mark.agarunov Mark Agarunov
            irshadahmed.ansari@gmail.com Irshad Ansari
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: