Left Outer Join using Mongo 3.0

[kanthu]

Hello All, 

I'm trying to perform left outer join by mapreduce. I have a common field in both collections. I need to generate a new collection where collection1 data's demographic information needs to be present in new collection. I typically need to perform "left outer join"

Collection1 [5 documents]

 GUID

 EXT_ACCT_ID

DEVICE_ID

 CATALOG_ID

TIME:

collection2 [59000 documents]

ACCT_ID

NAME:

Output collection must be 5 documents of collection1 with ACCT_ID and NAME fields and its values. 

[Wan Bachtiar]

Output collection must be 5 documents of collection1 with ACCT_ID and NAME fields and its values.

Hi Kanthu,

Instead of performing join on the server via MapReduce, you should try to perform the join on client application side. If you are performing the join operation often, perhaps you should consider to re-design your schema by embedding the information. See Data Model Examples and Patterns for more information on schema design.

If you are in the position to be able to upgrade to MongoDB v3.2, you could try $lookup aggregation operator to perform left-outer equi-joins. As an example, let’s say you have collection1 of:

{   guid: 2000,
    ext_acct_id: 101,
    device_id: 9000,
    catalog_id: 800
},
{   guid: 2001,
    ext_acct_id: 201,
    device_id: 9090,
    catalog_id: 808
}

and collection2 consists of (assuming acct_id is not _id):

{   acct_id: 101,
    name: "John Smith"
},
{   acct_id:101,
    name: "Jane Smith"
},
{   acct_id:201,
    name: "Kent Brockman"

}

You can perform the lookup operation as below:

db.collection1.aggregate([
    { $lookup : {
        from:"collection2", 
        localField:"ext_acct_id", 
        foreignField:"acct_id", 
        as:"accounts"}
    }
])

Which should output something similar to :

{
  "guid": 2001,
  "ext_acct_id": 201,
  "device_id": 9090,
  "catalog_id": 808,
  "accounts": [
    {
      "acct_id": 201,
      "name": "Kent Brockman"
    }
  ]
},
{
  "guid": 2000,
  "ext_acct_id": 101,
  "device_id": 9000,
  "catalog_id": 800,
  "accounts": [
    {
      "acct_id": 101,
      "name": "John Smith"
    },
    {
      "acct_id": 101,
      "name": "Jane Smith"
    }
  ]
}

If the above example it’s not what you are after, could you clarify your use case please ? 

By providing document samples of:

• collection1
• collection2
• The output document that you would like to have.

Regards,

Wan.

​