July 10, 2015

Join MongoDB Collections with esProc


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 code: 

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: 

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.