[mongodb-user] 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.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a7424996-52d7-4205-97bc-2322e063d2bc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[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
<https://docs.mongodb.org/manual/core/map-reduce/>, 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
<https://docs.mongodb.org/manual/applications/data-models/> 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
<https://docs.mongodb.org/manual/reference/operator/aggregation/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.
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/07ad90dd-fa95-4764-a659-ce5ef23c8871%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.