CODEWITHSUNDEEP
mongodb
YMC
YMC

Reputation: 5462

How can I effectively join 2 huge collections in MongoDb?

I have two huge (few hundred thousands of records) collections Col1 and Col2, and I need to fetch joined data from both of them. There is a join criteria that lets me dramatically decrease number of records returned to few hundreds, so in SQL language I would run something like

 SELECT ... FROM Col1 INNER JOIN Col2 ON Col1.field1 = Col2.field2

and it would run pretty fast as Col1.field1 and Col2.field2 are indexed fields. Is there any direct way or workaround to do the same thing fast in MongoDb with indexes usage, not to scan all the items?

Note: I cannot redesign collections to merge them into one.

Upvotes: 4

Views: 8542

Answers (3)

0xcuonghx
0xcuonghx

Reputation: 590

the join in MongoDB is so expensive. 2 solutions:

  • Redesign merge them into one
  • limit, match before you join

Upvotes: 0

Yadi
Yadi

Reputation: 31

In order to join in MongoDb 4.2 you can use aggregation and $lookup like this query:

db.collection.aggregate([
   { $lookup: { from: "...", ... } }
])

It is usefull for me

More information: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

Upvotes: 3

jdi
jdi

Reputation: 92627

MongoDB has no JOIN so there is not a fast equivalent. It is most likely a schema design issue but you said you can't change that. You can't query multiple collections in one query.

You can either do the join client-side in 2 queries or you can do it in non-live style by doing a map-reduce and generating a 3rd collection.

Reference this other question for details on how to do a map-reduce

Upvotes: 5

Related Questions