Join MongoDB Collections with esProc

Problem source: http://stackoverflow.com/questions/29396985/is-there-a-where-like-relation-function-when-using-pymongo  .

It is difficult to join MongoDB collections through hardcoding as it doesn’t directly support joins. Yet you can use esProc to perform inner join, left join and full join between collections or join the documents. Here is an example.

Logically, the two collections – categories and rules – have a referenced and referencing relationship through the key field cat. They need to be joined using left join to retrieve titleregexcat field from categories and path field from rules. Below is a part of the original data:

esProc_NoSQL_mongodb_join_1

esProc code:

esProc_NoSQL_mongodb_join_2

A1: Connect to MongoDB. The connection string format is mongo://ip:port/db?arg=value&…

A2, B2: Retrieve data from MongoDB using find function, sort it and create a cursor. A2’s cursor contains data from the left table that requires sorting by cat. In esProc find function, which is analogous to the combination of MongoDB findsort and limitfunction, the filtering criterion syntax follows the MongoDB rules.

A3: Perform left join through the key field cat. @x means joining the two cursors. @1 means performing left join. The two options can work together.

A4: Retrieve desired field from A3. _1 and _2 respectively represent the two cursors under joining.

A5: Fetch data from the cursor as follows:

esProc_NoSQL_mongodb_join_3

One point to note is that if A4 produces too much data to be loaded into memory, you can use export function to write it into a file.

A6: Close MongoDB connection.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in MongoDB, SQL-related Puzzle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s